MySQL Forums
Forum List  »  MyISAM

slow query on myisam table
Posted by: Jan Kowalski
Date: March 04, 2014 06:48AM

I have server with many wordpress instalations and there is problem with this query:
SELECT option_name, option_value FROM wp_11_options WHERE autoload = 'yes'

CREATE TABLE `wp_options` (
`option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`option_name` varchar(64) NOT NULL DEFAULT '',
`option_value` longtext NOT NULL,
`autoload` varchar(20) NOT NULL DEFAULT 'yes',
PRIMARY KEY (`option_id`),
UNIQUE KEY `option_name` (`option_name`)
)

There is 689 wp_*_options tables on server and on average 500 rows per table. There is no need to index autoload column because there is 'yes' value in about 80% of rows

this is output of mq-query-digest:
# Query 1: 0.12 QPS, 0.22x concurrency, ID 0xFEC9B62C781B75A2 at byte 380885
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.51 [1.0], V/M = 0.36
# Query_time sparkline: | ^ |
# Time range: 2014-03-04 11:41:58 to 13:01:08
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 35 591
# Exec time 28 1042s 1s 9s 2s 3s 792ms 1s
# Lock time 54 10s 31us 724ms 18ms 87ms 78ms 66us
# Rows sent 43 141.30k 153 289 244.82 271.23 24.34 234.30
# Rows examine 0 237.80k 225 600 412.02 563.87 89.39 381.65
# Query size 13 42.61k 73 75 73.83 72.65 0 72.65
# String:
# Databases zaplecze2_... (219/37%), zaplecze5_... (99/16%)... 3 more
# Hosts portal-portali.pl
# Users zaplecze2_... (219/37%), zaplecze5_... (99/16%)... 3 more
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# 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


Is there any way to optimise this query?

Options: ReplyQuote


Subject
Views
Written By
Posted
slow query on myisam table
3636
March 04, 2014 06:48AM
1627
March 08, 2014 12:32PM
1513
March 10, 2014 05:23AM
1576
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.