MySQL Forums
Forum List  »  MyISAM

Re: slow query on myisam table
Posted by: Rick James
Date: March 13, 2014 08:29PM

> but I have all of this in about separate 50 databases and on average 120 tables in one db

Ok, that is only a few thousand files per directory. That would not explain 1+ sec/query.

So, let's dig further.

Are you on Windows?

mysql> SHOW VARIABLES LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 1424  |
| table_open_cache           | 2048  |

What values do you have?

> There is 689 wp_*_options tables on server

Spread across 50 directories?

Please provide output from (as suggested in the pt-query-digest):
SHOW TABLE STATUS FROM `zaplecze2_pizpl` LIKE 'wp_11_options'\G
SHOW CREATE TABLE `zaplecze2_pizpl`.`wp_11_options`\G
EXPLAIN /*!50100 PARTITIONS*/ SELECT option_name, option_value FROM wp_11_options WHERE autoload = 'yes'\G

> this is output of mq-query-digest:

Suggest you change to pt-query-digest; it is much newer.

> Is there any way to optimise this query?

What percent of the rows have 'yes'? If less than 10%, then INDEX(autoload) will help.

(My rant at WordPress still stands.)

Options: ReplyQuote


Subject
Views
Written By
Posted
3636
March 04, 2014 06:48AM
1626
March 08, 2014 12:32PM
1512
March 10, 2014 05:23AM
Re: slow query on myisam table
1575
March 13, 2014 08:29PM


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.