MySQL Forums
Forum List  »  Stored Procedures

Re: Query times out when I add extra Where Condition
Posted by: Peter Brawley
Date: January 27, 2020 12:59PM

How much of the 8GB is available to MySQL? Assuming half ie 4GB, then innodb_buffer_pool_size should be about 3GB.

For a very rough idea of the mem demands of a query, multiply all the row estimates in the Explain result. On that measure, your query is looking at billions of rows. I seriously doubt 4GB on a Windows system is anywhere adequate for that.

For a different estimate estimate of the memory demands of that query, let's see the result of ...

select engine,data,indexes,total
from (
    ifnull(engine,'TOTALS') as engine, 
    concat(data,' GB') as data, 
    concat(indexes,' GB') as indexes, 
    concat(tot,' GB') as total,
    if(engine is null,-1,tot) as ord
  from (
      round( sum(data_length)/1024/1024/1024, 2 ) as data,  
      round( sum(index_length)/1024/1024/1024, 2 ) as indexes,  
      round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot
    from information_schema.tables
    where table_schema-... and table_name in(...)
    group by engine with rollup
  ) sums
) list
order by list.ord desc;

substituting the quoted schema name and tablk names for the ellipses.

I don't know how you produced your DDL, but without spending half an hour massaging it I can't work with it, so please post it in human-readable form the simplest way is to run the Show Create Table cmds terminated by \G in the mysql client program, copy & paste the results.

Options: ReplyQuote

Written By
Re: Query times out when I add extra Where Condition
January 27, 2020 12:59PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.