возможно ли оптимизировать подобный запрос?

[camka]

возможно ли оптимизировать подобный запрос?

Выбираю TOP самых искомых слов за заданный период времени (id between xxx and xxx) для определенного поисковика(ов) (engine in (x,xx,xxx)).

Таблица слов
-----------------
id - уник. идентификатор слова (первичный ключ)
k - само искомое слово/фраза (уникальный индекс)

Таблица статистики
--------------------------
id - дата(день) (индексировано, является первой частью составного индекса)
hits - количество раз, сколько искали фразу
query - ссылка на слово(id) в таблице слов (индексировано)
engine - идентификатор поисковика
и еще индекс для (engine, id)


[sql]
select r.k as word, sum(s.hits) as hits from words r inner join stats s on r.id = s.query where s.engine in (3) and s.id between 20041101 and 20041207 group by r.id order by hits desc, r.k limit 100 ;
[/sql]

+--------------------------+------+
100 rows in set (5 min 22.58 sec)

table words - 200 000 zapisej
table stats - 300 000 zapisej

EXPLAIN

+-------+--------+--------------------+-----------+---------+---------+--------+----------------------------------------------+
| table | type   | possible_keys      | key       | key_len | ref     | rows   | Extra                                        |
+-------+--------+--------------------+-----------+---------+---------+--------+----------------------------------------------+
| s     | ref    | id,query,engine_id | engine_id |       4 | const   | 153874 | Using where; Using temporary; Using filesort |
| r     | eq_ref | PRIMARY            | PRIMARY   |       4 | s.query |      1 |                                              |
+-------+--------+--------------------+-----------+---------+---------+--------+----------------------------------------------+

Проблема в том, что если, как показано в запросе, период слишком большой, составной индекс engine_id используется не полностью, что понятно. Но скорость запроса значительно замедляется, что неприемлемо.
Возможно ли как то оптимизировать этот запрос???

Для сравнения привожу запрос с меньшим временным периодом.
[sql]
select r.k as word, sum(s.hits) as hits from words r inner join stats s on r.id = s.query where s.engine in(3) and s.id between 20041201 and 20041207 group by r.id order by hits desc, r.k limit 100 ;
[/sql]

+-------+--------+--------------------+-----------+---------+---------+-------+----------------------------------------------+
| table | type   | possible_keys      | key       | key_len | ref     | rows  | Extra                                        |
+-------+--------+--------------------+-----------+---------+---------+-------+----------------------------------------------+
| s     | range  | id,query,engine_id | engine_id |       8 | NULL    | 15976 | Using where; Using temporary; Using filesort |
| r     | eq_ref | PRIMARY            | PRIMARY   |       4 | s.query |     1 |                                              |
+-------+--------+--------------------+-----------+---------+---------+-------+----------------------------------------------+

2 rows in set (0.03 sec)

Может быть существуют стандартные решения, как избавиться от Using temporary; Using filesort ???

спасибо.

 

[chira]

а если такой запрос сделать?
[SQL]
SELECT s.query, sum( s.hits ) AS hits
FROM stats s
WHERE s.engine IN ( 3 )
AND s.id BETWEEN 20041101 AND 20041207
GROUP BY s.query
[/SQL]
сколько строк вернёт, какой explain?

 

[camka]

+-------+------+---------------+-----------+---------+-------+--------+----------------------------------------------+
| table | type | possible_keys | key       | key_len | ref   | rows   | Extra                                        |
+-------+------+---------------+-----------+---------+-------+--------+----------------------------------------------+
| s     | ref  | id,engine_id  | engine_id |       4 | const | 153874 | Using where; Using temporary; Using filesort |
+-------+------+---------------+-----------+---------+-------+--------+----------------------------------------------+
1 row in set (0.01 sec)

+--------+------+
184955 rows in set (10.63 sec)

 

[chira]

у тебя данных в таблице stats за 2 месяца? (возвращается 2/3 записей)
а если создать временную таблицу и потом объеденить её с words?

а ты не хочешь сделать суммарную таблицу из stats, данные обновлять раз в сутки?
и выборку делать из этой суммарной ...

query, id, hits_engine1,hits_engine2 ... (если engine не очень много)
Primary Key(id, query)

 

[camka]

Количество сайтов переодически дополняется, меняется, поэтому такое решение не подходит.

Сейчас задачу решил так. Делаю временную таблицу

create temporary table tmptbl as select query, sum(hits)as hits from stats where engine in (3) and id between 20041101 and 20041204 group by query order by hits desc limit 1000;

и объединяю ее с таблицей слов.

select r.k as word, s.hits from word r, tmptbl s where r.id=s.query order by hits desc, r.k;

получаю те же самые 1000 нужных записей. Минус здесь тот, что если период времени мал, то индекс использовался бы и на прошлом запросе, а здесь получается тормознее, так как надо создавать временную таблицу. А для создания используется filesort и temporary, как в общем то и для сортировки по выборке. Но поскольку в выборке принимает участие только 1000 записей, то это не бросается в глаза. Поэтому я извратился и сначала проверяю, сколько записей будет участвовать из таблицы stats.

select count(*) from stats where engine in (3) and id between 20041101 and 20041204;

сравниваю их с общим количеством записей в этой таблице

select count(*) from stats;

и если первый запрос возвращает больше, чем 1/3 от второго, то использую решение с временной таблицей. Иначе - старый способ. Да, там еще сделал group by не по r.k, а по s.query, так что он теперь иногда использует индекс query.

Я согласен, что это извращение но пока работает. Максимум 10-15 секунд уходит на статистику за весь период времени, это резвее чем раньше. Но надеюсь еще как то оптимизировать, потому как количество данных с каждым днем растет. И в основнов в таблице статистики.

 

[chira]

Количество сайтов переодически дополняется, меняется, поэтому такое решение не подходит.

Здесь "не подходит" может быть если это происходит автоматически, без твоего участия. Или кол-во сайтов порядка 100 ...
Сначала оцени, даст ли ощутимый выигрыш применение суммарной таблицы.

а почему ты делаешь limit 1000? у тебя Top1000?

 

[camka]

да. Top1000. Но цифра тоже может меняться как в большую, так и в меньшую сторону. Если брать статистику по всем словам (в данный момент, как я уже успел отметить, их 200 000), то можно и крякнуть.

База используется помимо моего приложения для подсчета статистики еще в _нескольких_ приложениях, и если переделывать структуру, то предется переделывать и все эти приложения, что нежелательно.

Сначала оцени, даст ли ощутимый выигрыш применение суммарной таблицы.

это той, что с полями для каждого поисковика или моей временной?

 

[chira]

полями для каждого поисковика

 

[martovsky]

SELECT
	r.k AS word,
	sum(s.hits) AS hits
FROM
	words r,
	stats s
WHERE
	r.id = s.query AND
	s.engine IN (3) AND
	s.id BETWEEN 20041101 AND 20041207
GROUP BY
	r.id
ORDER BY
	hits DESC, r.k
LIMIT 100 ;

попробуй вот так. ибо join тормозной, думаю запрос будет сильно быстрее отрабатываться.

 

[camka]

Автор оригинала: martovsky

попробуй вот так. ибо join тормозной, думаю запрос будет сильно быстрее отрабатываться.

То, что ты написал, это тот же самый джойн, только неявный. Работает так же но синтаксис альтернативный.

 

[martovsky]

Автор оригинала: camka
То, что ты написал, это тот же самый джойн, только неявный. Работает так же но синтаксис альтернативный.

я в курсе. я тоже занимаюсь обсчетом статистики посещаемости сайтов, и во многих отчетах у меня с join'ом тормоза, а вот таким образом - скорость гораздо выше.

возможно еще стоить посмотреть и попробовать поставить индексы на поля, по которым идет выборка.

ты попробуй, скажи про тайминги. мне тоже интересно.

 

[camka]

Автор оригинала: martovsky
... и во многих отчетах у меня с join'ом тормоза, а вот таким образом - скорость гораздо выше...

Это невозможно, поскольку это ОДНО И ТО ЖЕ. Приведи пример своего тормозного запроса с джойном и аналогичного без джойна и их EXPLAIN'ы.

 

[.des.]

camka FORCE INDEX не пробовали?

 

[camka]

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

 

[martovsky]

Автор оригинала: camka
Это невозможно, поскольку это ОДНО И ТО ЖЕ. Приведи пример своего тормозного запроса с джойном и аналогичного без джойна и их EXPLAIN'ы.

я тоже думал что невозможно. практика показала что возможно, даже очень.

оки, буду на работе - попробую сделать тест и отписать тайминг.

 

[camka]

Автор оригинала: .des.
camka FORCE INDEX не пробовали?

Если имелось ввиду force index(engine_id), то в принципе он и так используется. Прописание его никак не помогло.

martovsky
я тоже думал что невозможно. практика показала что возможно, даже очень.

Не поверю, пока сам не увижу.