Сортировка по кол-ву просмотров

[sage]

Сортировка по кол-ву просмотров

Доброе время суток всем.

Есть 2 таблицы: таблица товаров catalogue_catalogue и таблица просмотров этих товаров catalogue_views_log (идтовара, IP, дата). В catalogue_catalogue 8383 записей, в catalogue_views_log - 366497. В выводе по разделу требуется выводить все товары этого раздела и всех дочерних, сортируя по кол-ву просмотров за неделю. Запрос

SELECT SQL_CALC_FOUND_ROWS `t1`.`modelid` 
FROM (
`catalogue_catalogue` `t1`
)
LEFT JOIN `catalogue_views_log` `t6` ON `t6`.`modelid` = `t1`.`modelid` AND `t6`.`viewed`
BETWEEN `t6`.`viewed` - INTERVAL 1 WEEK AND NOW( )
WHERE `t1`.`catid` IN ( перечисление 42-х категорий ) AND `t1`.`on_offer` =1
GROUP BY `t6`.`modelid`
ORDER BY COUNT( `t6`.`modelid` ) DESC
LIMIT 0 , 21

выполняется 0.88 сек. Если убрать LEFT JOIN - 0.03 сек.

Как можно оптимизировать запрос?

Пока я вижу только один способ: убрать LEFT JOIN, добавить в таблицу товаров столбец views_count и писать в него кол-во просмотров за неделю, и при выборке сортировать по этому полю. Есть какие-нить другие варианты? Кто как делал? Спасибо.

 

[Fortop]

explain где?

 

[sage]

id     select_type  table   type   possible_keys     key        key_len    ref                   rows     Extra
1      SIMPLE       t1      ref    catid,on_offer    on_offer     1        const                 3523     Using where; Using temporary; Using filesort
1      SIMPLE       t6      ref    modelid,viewed    modelid      3        admin_amili.t1.modelid  45

 

[Fortop]

Ну как бы записей немного даже с учетом filesort.

А такой вопрос

GROUP BY `t6`.`modelid`
ORDER BY COUNT( `t6`.`modelid` ) DESC

в таком раскладе COUNT(t6.modelid) сильно нужен?
что если избавиться от него?

-~{}~ 02.04.10 03:22:

не-не, лучше верните как было без подсветки.

 

[sage]

мы ж сортируем, поэтому COUNT обязателен. К тому же, бед не он добавляет

 

[Fortop]

К тому же, бед не он добавляет

Как раз он и добавляет.

выражения в ORDER BY не дают возможность использовать индекс.
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

-~{}~ 02.04.10 03:26:

Плюс без фикстуры быстро не проверить, но не будут ли там везде 1-чки ?

 

[sage]

убираю сортировку вообще - 0.76 сек. не так уж и сильно влияет

 

[Fortop]

Хотя может вы и правы, там еще join может мешать поскольку сортируется по нему.

Есть возможность выложить create table и небольшую фикстуру для БД?

-~{}~ 02.04.10 03:31:

Еще, таблицы сильно большие? Что если сделать OPTIMIZE обоим?
Время сильно странное для такого количества данных.

 

[prolis]

это хороший пример для хранения агрегатов.
создай таблицу catalogue_views_lastweek:
[sql]
drop table catalogue_views_lastweek;
create table catalogue_views_lastweek as
select modelid,viewed from catalogue_views_log
where viewed>now()-INTERVAL 1 WEEK
group by modelid;
ALTER TABLE catalogue_views_lastweek ADD PRIMARY KEY (modelid);
[/sql]
и запускать раз в час/день/неделю

 

[sage]

хорошо. спасибо. тоже вариант есть ещё какие-нить?) чтоб знать. или это наиболее оптимальный?

-~{}~ 02.04.10 18:13:

наверное, все варианты всё равно сводятся к сортировке по заранее сохранённому кол-ву просмотров.

 

[Gas]

наверное, все варианты всё равно сводятся к сортировке по заранее сохранённому кол-ву просмотров.

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

 

[Fortop]

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

Большого количества-то и нету...
explain посмотрите.

 

[Gas]

По explain'у получается что обрабатывается 3523 категорий к каждой из которых в среднем приджойнивается по 45 записей, потом этот рекордсет записей группируется. 150K записей это не мало.

 

[Fortop]

Хотите фокус?

2 таблицы 1500 записей и 15млн записей во второй

mysql> [sql]SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id WHERE aFCK=6 GROUP BY tours.htlCoK ORDER BY COUNT(tours.htlCoK);[/sql]
..... скипнуто ....
256 rows in set (0.67 sec)                                                                                                                                                                                    

mysql> explain SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id WHERE aFCK=6 GROUP BY tours.htlCoK ORDER BY COUNT(tours.htlCoK);
+----+-------------+---------+--------+----------------------+----------+---------+-------------------+-------+----------------------------------------------+
| id | select_type | table   | type   | possible_keys        | key      | key_len | ref               | rows  | Extra                                        |
+----+-------------+---------+--------+----------------------+----------+---------+-------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | tours   | ref    | afck,htlcok,combined | combined | 3       | const             | 28848 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | tours_p | eq_ref | PRIMARY              | PRIMARY  | 4       | test.tours.htlCoK |     1 | Using where; Using index                     | 
+----+-------------+---------+--------+----------------------+----------+---------+-------------------+-------+----------------------------------------------+ 
2 rows in set (0.00 sec)

При 10к записей в первой таблице запрос выполняется до 7с с почти таким же explain

mysql> explain SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id WHERE aFCK = 24 GROUP BY tours.htlCoK LIMIT 0,20;
+----+-------------+---------+--------+----------------------+----------+---------+-------------------+-------+--------------------------+
| id | select_type | table   | type   | possible_keys        | key      | key_len | ref               | rows  | Extra                    |
+----+-------------+---------+--------+----------------------+----------+---------+-------------------+-------+--------------------------+
|  1 | SIMPLE      | tours   | ref    | afck,htlcok,combined | combined | 3       | const             | 34338 | Using where              | 
|  1 | SIMPLE      | tours_p | eq_ref | PRIMARY              | PRIMARY  | 4       | test.tours.htlCoK |     1 | Using where; Using index | 
+----+-------------+---------+--------+----------------------+----------+---------+-------------------+-------+--------------------------+ 
2 rows in set (0.00 sec)                                                                                                                   

mysql> explain SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id AND aFCK = 44 WHERE id BETWEEN 10 AND 90 GROUP BY tours.htlCoK LIMIT 0,20;
+----+-------------+---------+-------+----------------------+----------+---------+-----------------------+------+-----------------------------------------------------------+
| id | select_type | table   | type  | possible_keys        | key      | key_len | ref                   | rows | Extra                                                     |
+----+-------------+---------+-------+----------------------+----------+---------+-----------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | tours_p | range | PRIMARY              | PRIMARY  | 4       | NULL                  |   81 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | tours   | ref   | afck,htlcok,combined | combined | 6       | const,test.tours_p.id |   29 | Using where                                               | 
+----+-------------+---------+-------+----------------------+----------+---------+-----------------------+------+-----------------------------------------------------------+ 
2 rows in set (0.00 sec)                                                                                                                                                      

mysql> explain SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id AND aFCK = 144 WHERE id BETWEEN 10 AND 90 GROUP BY tours.htlCoK ORDER BY COUNT(myid) LIMIT 0,20;
+----+-------------+---------+-------+----------------------+----------+---------+-----------------------+------+-----------------------------------------------------------+
| id | select_type | table   | type  | possible_keys        | key      | key_len | ref                   | rows | Extra                                                     |
+----+-------------+---------+-------+----------------------+----------+---------+-----------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | tours_p | range | PRIMARY              | PRIMARY  | 4       | NULL                  |   81 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | tours   | ref   | afck,htlcok,combined | combined | 6       | const,test.tours_p.id |   29 | Using where                                               | 
+----+-------------+---------+-------+----------------------+----------+---------+-----------------------+------+-----------------------------------------------------------+ 
2 rows in set (0.00 sec)                                                                                                                                                      

mysql> explain SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id AND aFCK = 72 WHERE id BETWEEN 10 AND 90 GROUP BY tours.htlCoK LIMIT 0,20;
+----+-------------+---------+-------+----------------------+----------+---------+-----------------------+------+-----------------------------------------------------------+
| id | select_type | table   | type  | possible_keys        | key      | key_len | ref                   | rows | Extra                                                     |
+----+-------------+---------+-------+----------------------+----------+---------+-----------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | tours_p | range | PRIMARY              | PRIMARY  | 4       | NULL                  |   81 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | tours   | ref   | afck,htlcok,combined | combined | 6       | const,test.tours_p.id |   29 | Using where                                               |
+----+-------------+---------+-------+----------------------+----------+---------+-----------------------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

+----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration    | Query                                                                                                                                                         |
+----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 | 21.98517300 | SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id WHERE aFCK = 24 GROUP BY tours.htlCoK LIMIT 0,20                                                |
|        2 |  6.40927400 | SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id AND aFCK = 44 WHERE id BETWEEN 10 AND 90 GROUP BY tours.htlCoK LIMIT 0,20                       |
|        3 |  6.91070600 | SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id AND aFCK = 144 WHERE id BETWEEN 10 AND 90 GROUP BY tours.htlCoK ORDER BY COUNT(myid) LIMIT 0,20 |
|        4 |  6.34701800 | SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id AND aFCK = 72 WHERE id BETWEEN 10 AND 90 GROUP BY tours.htlCoK LIMIT 0,20                       |
+----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

 

[Gas]

Хотите фокус?

не против если на ты?

analyze table перед этими запросами:

explain SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id WHERE aFCK=6 GROUP BY tours.htlCoK ORDER BY COUNT(tours.htlCoK);

explain SELECT * FROM tours_p JOIN tours ON tours.htlCoK = tours_p.id WHERE aFCK = 24 GROUP BY tours.htlCoK LIMIT 0,20;

не делался?

в любом случае, значение rows в explain это только приближённое значение, основанное на cardinality индекса (если не ошибаюсь) и может очень сильно не совпадать с реальным значеним. Это Rows_examined в slow_log'е показывает точное количество обработанных записей. Но несмотря на то, что explain может ошибаться (например, до версии 5.1 запросы с limit по индексу всё равно показывали полное количество строк без его учёта), если он говорит о recordset'е 3523 * 45, то врят-ли речь идёт о паре тысяч записей, скорее всего значительно больше.
Я к тому, что по большому счёту мы оба не совсем правы, когда говорим о большой или маленькой выборке только на основе explain'а.

Что же касается ситуации sage, я однозначно считаю что не стоит в реал тайме делать джоин и группировку его таблиц, а по другому быстро посчитать врят-ли возможно.

 

[Fortop]

не против если на ты?

да ради бога.

не делался?

Делался. Но фокус не в этом
При росте 1й таблицы в 6ть раз, запрос замедляется в 30 раз. Хотя explain показывает всего лишь +6к записей

Смотреть надо в каждом конкретном случае. Например, можно в определенных ситуациях добиться, чтобы GROUP BY использовал индекс.
При том, что конечных результатов будет в пределах 1-2 сотен, возможно, и LIMIT и SQL_CALC_FOUND_ROWS можно убрать,
соответственно убрав ORDER BY expression и т.д.

Просто ТС'у лень этим заниматься

 

[sage]

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

 

[Gas]

При росте 1й таблицы в 6ть раз, запрос замедляется в 30 раз. Хотя explain показывает всего лишь +6к записей

это я понял, ничто не совершенно, тем более mysql

Смотреть надо в каждом конкретном случае.

полностью согласен

Просто ТС'у лень этим заниматься

просто в его ситуации вполне реально что при показе, например, 50 товаров, их за неделю посмотрели 100K раз (записей в логе), тут как не экспериментируй, всё равно запрос будет не быстрым если не делать полей-счётчиков или таблиц с аггрегированными данными.

 

[sage]

Fortop
у меня без сортировок, без лимитов и т.д. выведется 1402 товара в этом и всех дочерних разделах. думаешь, если я всё уберу, будет быстрее?

 

[Gas]

sage
тут самое важное сколько просмотров у них, то есть сколько записей в catalogue_views_log подпадает под твоё условие.