CODEWITHSUNDEEP
mysqlwordpressfilesorting
Wed
Wed

Reputation: 23

Optimize wordpress mysql database filesort in 415885 lines

mysql> explain SELECT  t.*, tt.* FROM wp_terms AS t  INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('post_tag') AND tt.count > 0 ORDER BY tt.count DESC LIMIT 45;
+----+-------------+-------+------------+--------+---------------------------+----------+---------+--------------------------------------------------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys             | key      | key_len | ref                                              | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+--------+---------------------------+----------+---------+--------------------------------------------------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | tt    | NULL       | ref    | term_id_taxonomy,taxonomy | taxonomy | 130     | const                                            | 415885 |    33.33 | Using index condition; Using where; Using filesort |
|  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY                   | PRIMARY  | 8       | dosk_com_app20413575705d6c60930e2af.tt.term_id |      1 |   100.00 | NULL                                               |
+----+-------------+-------+------------+--------+---------------------------+----------+---------+--------------------------------------------------+--------+----------+----------------------------------------------------+

Every time the site is accessed is doing filesort in 415885 lines which many times take like 14 seconds. Any idea how can I optimize this ?

thanks

Upvotes: 0

Views: 45

Answers (1)

jdog
jdog

Reputation: 2549

The filesort happens because of the order by, however I cannot see how you would be able to eliminate that within a system like Wordpress. At least try the explain statement without order by to make sure this is the case.

I would look at the following paths for optimisation

  • Cache the page
  • The query should be cacheable, is MySQL query cache on?
  • wp_term_taxonomy sounds like a link table which should be quite small per row. So a table scan should not take 14 seconds. This would point to lack of disk performance

Upvotes: 1

Related Questions