MySQL Forums
Forum List  »  Performance

Re: Very slow query for no apparent reason
Posted by: Rick James
Date: December 09, 2010 11:49AM

Let me see the VARIABLEs so I can advise on table_cache, etc.
I discuss table_cache here:
http://mysql.rjweb.org/doc.php/memory

Select  *
    from  stats_sit
    where  matrixID = 882014
      and  agg_time + 60 <= end_time
    limit  0, 5000.
INDEX(matrixID) -- this will probably help.
INDEX(matrixID, agg_time, end_time) -- might be a little better
and agg_time + 60 <= end_time -- This is too complex to be optimized.
matrixID is needed because of "where matrixID = 882014".
Indexing the times is useless because of the computation between the two. However, the compound index I suggest _may_ spend more time in the 'index' and less time in the 'data'. The EXPLAIN may say "Using index".

[ code ] -- remove the spaces.

"I use 1000 connections" -- meaning max_connections = 1000 ? That is a large number. Where do they come from? Web servers?

Please do (and use the code tags without spaces):
EXPLAIN select id, name from stats_applications where name in ('134','126','132','128','138','122','136','124','120','130')
The names seem to be numbers, what that intentional?

SHOW TABLE STATUS LIKE 'stats_applications'\G

update stats_filesystem set firstfreeslot = 8294 -- there is no WHERE clause??

SELECT  alert_id, acc_destination_id
    FROM  alerts, links
    WHERE  links.link_id=676961
      and  alerts.link_id = 676961
Ouch! There is nothing tying the two table together!

Options: ReplyQuote


Subject
Views
Written By
Posted
5119
December 02, 2010 06:45AM
Re: Very slow query for no apparent reason
1158
December 09, 2010 11:49AM


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.