MySQL Forums
Forum List  »  Optimizer & Parser

Re: Problem with a query performance
Posted by: Rick James
Date: January 07, 2012 10:52AM

"LIMIT 10" hides part of the issue. Remove that, and you will probably find that "futbol" occurs a lot more than "baloncesto". Since the query has to do at least one "filesort" (I think it is doing two), it has to collect more rows for "futbol", hence takes longer.

You have a lot of indexes; this probably slows down INSERTs, but perhaps not enough to matter. Even so, the one that I think is useful is missing:
INDEX (SITE, SECCION, TIMESLOT), or
INDEX (SECCION, SITE, TIMESLOT)

What is the value of innodb_file_per_table? I suspect it is 0. (If it is 1, then I need to bring up another topic.)

If you have not increased innodb_buffer_pool_size from the default, then please read this; it may improve performance significantly:
http://mysql.rjweb.org/doc.php/memory
(That's why I asked for SHOW VARIABLES LIKE '%buffer%';)
With only 155MB of data, I would not expect such long query times unless it is unnecessarily hitting the disk.

When you run timings, I suggest you run each test twice in a row. The first time _may_ be hitting the disk to get blocks that are not cached (in the buffer_pool); the second time should be more useful as an indicator of efficiency. (Thanks for doing RESET QUERY CACHE; that eliminates it from the equation.)

Options: ReplyQuote


Subject
Views
Written By
Posted
2509
January 04, 2012 01:43PM
1082
January 05, 2012 10:41AM
1007
January 06, 2012 12:36PM
Re: Problem with a query performance
995
January 07, 2012 10:52AM


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.