> 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.)