Yii Framework

Всем доброго времени суток!
Не так давно просили посмотреть один сайтик на Wordpress, так вот некоторые выборки там выполняются просто довольно медленно.
Вкратце, есть таблица wp_postmeta с ~480 000 записей где прожорливый WP хранит аттрибуты постов с вот такой структурой:

Код: Выделить всё

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| meta_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| post_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

У каждого поста есть около десятка записей в этой таблице одна из которых с meta_key='views' и числом просмотров в meta_value, так вот нужно сделать выборку из 3 постов с максимальным количеством просмотров. Я делаю так:

Код: Выделить всё

SELECT SQL_NO_CACHE meta_value+0 FROM wp_postmeta WHERE meta_key='views' ORDER BY (meta_value+0) DESC LIMIT 3; выполняется за 0,20 сек

Код: Выделить всё

Результат EXPLAIN SELECT SQL_NO_CACHE meta_value+0 FROM wp_postmeta WHERE meta_key='views' ORDER BY (meta_value+0) DESC LIMIT 3;
+----+-------------+---------------------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+----------------+
|  1 | SIMPLE      | wp_postmeta | ALL  | NULL          | NULL | NULL    | NULL | 479223 | Using filesort |
+----+-------------+---------------------+------+---------------+------+---------+------+--------+----------------+

Так вот можно ли как то сделать эту выборку быстрее и как побороть 'Using filesort'?

Почему бы вам просто не засечь как есть и как делаете вы? И что означает meta_value+0 ?

solo писал(а):Почему бы вам просто не засечь как есть и как делаете вы? И что означает meta_value+0 ?

Как видите поле meta_value текстовое, а сортировка нужна числовая, потому делаю так.

Вообще оригинальный запрос из WP звучит так:

Код: Выделить всё

SELECT DISTINCT wp_ru_tradeposts . * , ( meta_value +0 ) AS views FROM wp_ru_tradeposts LEFT JOIN wp_ru_tradepostmeta ON wp_ru_tradepostmeta.post_id = wp_ru_tradeposts.ID WHERE post_date < '2013-05-07 19:54:33' AND post_type = 'post' AND post_status = 'publish' AND meta_key = 'views' AND post_password = '' ORDER BY views DESC LIMIT 3;

И выполняется он 6-7 секунд =) Я его разделил на два более простых запроса и получил время выполнения около 0,37-0,40 сек, дольше выполняется именно первая часть сортировкой которую я и привел в топике. Хотел магического ускорения до тысячных долей секунды, но пока никак )

Индексы проставьте на поля, по которым условие - время существенно сократится.

Nafania писал(а):Индексы проставьте на поля, по которым условие - время существенно сократится.

Так в этом и цимес, пробовал ставить и отдельно на meta_key, meta_value, так и комбинированные на них обоих - результат не меняется. Может я чего не так делаю.

Делайте EXPLAIN и смотрите что к чему.

Nafania писал(а):Делайте EXPLAIN и смотрите что к чему.

Мсье тред не читал? =)

В первом же посте данного топика приводится вывод с EXPLAIN этого же запроса. Вот думаю может небольшой key_buffer всему виной.

Тред читал, но у вас EXPLAIN для запроса без индекса. А где EXPLAIN для запроса с индексом на meta_key и meta_value?

Nafania писал(а):Тред читал, но у вас EXPLAIN для запроса без индекса. А где EXPLAIN для запроса с индексом на meta_key и meta_value?

Добавил индекс на поле meta_key:

Код: Выделить всё

SHOW INDEXES FROM wp_postmeta;
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_ru_tradepostmeta |          0 | PRIMARY  |            1 | meta_id     | A         |      479223 |     NULL | NULL   |      | BTREE      |         |               |
| wp_ru_tradepostmeta |          1 | idx      |            1 | meta_key    | A         |          38 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


EXPLAIN SELECT SQL_NO_CACHE meta_value+0 FROM wp_postmeta WHERE meta_key='views' ORDER BY (meta_value+0) DESC LIMIT 3;
+----+-------------+---------------------+------+---------------+------+---------+-------+-------+-----------------------------+
| id | select_type | table               | type | possible_keys | key  | key_len | ref   | rows  | Extra                       |
+----+-------------+---------------------+------+---------------+------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | wp_ru_tradepostmeta | ref  | idx           | idx  | 768     | const | 45485 | Using where; Using filesort |
+----+-------------+---------------------+------+---------------+------+---------+-------+-------+-----------------------------+

Стало выполняться за 0,16 сек. Но быстрее пока никак.

попробуйте индекс на meta_value, только не целиком на все поле, а по нескольким первым символам. посчитайте селективность, если хорошая то пробуйте.

because писал(а):попробуйте индекс на meta_value, только не целиком на все поле, а по нескольким первым символам. посчитайте селективность, если хорошая то пробуйте.

Пробовал сделать на первые 10 символов индекс - без изменений. А что есть селективность?

Yii Framework

Ускорить SQL-запрос

Ускорить SQL-запрос

Re: Ускорить SQL-запрос

Re: Ускорить SQL-запрос

Re: Ускорить SQL-запрос

Re: Ускорить SQL-запрос

Re: Ускорить SQL-запрос

Re: Ускорить SQL-запрос

Re: Ускорить SQL-запрос

Re: Ускорить SQL-запрос

Re: Ускорить SQL-запрос

Re: Ускорить SQL-запрос