MySQL. Создание индекса по "тексту", избавление от Temporary Table.

[Schumi]

MySQL. Создание индекса по "тексту", избавление от Temporary Table.

Очень глубоко в суть вопроса вдаваться пока не буду, вдамся если на вопросы ниже так и не найдется ответ.
Менее конкретно - есть база. В мускуле. В ней всего одна таблица - 36 млн. записей. 3 Гига - дата+индексы.

Посл долгого сотавления конкретного запроса, вышел запрос, который использует в себе Temporary Table для реализации возможности сделать запрос, используя как набор данных - другой запрос.

Вопрос 1. Реально ли в MySQL в качестве набора данных, по которому делается выборка, использовать результат другого запроса, без использования CREATE TEMPORARY TABLE ...

Мнемонически, типа
SELECT * FROM (SELECT `a`,`b` FROM `table` GROUP BY `a`) WHERE `a` > 3
* Пример детский, но суть вопроса отражает.


Вопрос 2. <Здесь, был вопрос про индекс по текстовому полю, но я его удалил, ибо понял, что при выборках LIKE "%Key%" индекс не используется. >

P.S. Средненький такой запрос с этой таблицей занимает 60-90 секунд..


Спасибо!

 

[baev]

Мнемонически, типа
SELECT * FROM (SELECT `a`,`b` FROM `table` GROUP BY `a`) WHERE `a` > 3
* Пример детский, но суть вопроса отражает.

Блин, ну почему бы самому это запрос не проверить?
http://dev.mysql.com/doc/refman/4.1/en/subqueries.html

 

[chira]

Schumi

Посл долгого сотавления конкретного запроса, вышел запрос, который использует в себе Temporary Table для реализации возможности сделать запрос, используя как набор данных - другой запрос.

вот и давай свой запрос а не мнемонические

Мнемонически, типа
SELECT * FROM (SELECT `a`,`b` FROM `table` GROUP BY `a`) WHERE `a` > 3
* Пример детский, но суть вопроса отражает.

который можно переписать в один без подзапроса,
возможно с твоим реальным запросом можно сделать тоже самое ...
SELECT `a`,`b` FROM `table` WHERE `a` > 3 GROUP BY `a`

 

[Schumi]

Писал же, что пример детский....

Ладно. Вдаемся более глубоко....
Таблица имеет такой формат

ID UserID Query QueryTime
1 1 books ...
2 1 magazines ...
3 1 books ...
4 2 books ...
5 3 magazines ...
6 4 auto book ...

Т.е. какой-то человек когда-то что-то искал. Мне необходимо сделать ТОП запросов по популярности, причем от каждого UserID "засчитывать" только один Query, если есть несколько одинаковых.. Query ищеться по LIKE "%something%". Т.е. база огромна, а мне надо только то, что связанно с книгами... LIKE "%book%".

Таак.

В общем виде зпрос составил такой:

CREATE TEMPORARY TABLE `tmp` (`Query` TEXT);
LOCK TABLES `a` READ;
INSERT INTO `tmp` SELECT `Query` FROM `a` WHERE `Query` LIKE '%book%' GROUP BY `Query` , `UserID`;
SELECT `Query`, COUNT(*) AS `C` FROM `tmp` GROUP BY `Query` ORDER BY `C` DESC;
UNLOCK TABLES;
DROP TABLE `tmp`;


"Внутренний" запрос, если добавить показ одного поля (`UserID`)
SELECT `Query` , `UserID` FROM `a` WHERE `Query` LIKE '%book%' GROUP BY `Query` , `UserID`;

Выдаст следующее:

Query UserID
books 1
books 2
auto book 4

Второй (Внешний) же запрос
SELECT `Query`, COUNT(*) AS `C` FROM `tmp` GROUP BY `Query` ORDER BY `C` DESC;

Выдаст следующее :

Query C
books 2
auto book 1

Что и надо в итоге. Нормальный ТОП запросов....

Как без временной таблицы? 100% можно без нее, но в голову не лезет

-~{}~ 14.08.06 13:44:

baev, по вашей ссылке нет того, что я ищу.

Читаем внимательно мой вопрос:
"Реально ли в MySQL в _качестве набора данных_ , по которому делается выборка, использовать результат другого запроса, без использования CREATE TEMPORARY TABLE"

Я выделил основную фразу..

 

[vovik]

Первое что пришло в голову.

SELECT `Query`, COUNT(DISTINCT UserID)
FROM `a`
WHERE `Query` LIKE '%book%'
GROUP BY `Query`;

 

[baev]

Наверное, я в самом деле чего-то не понимаю...

SELECT b.query, COUNT( b.query ) AS `C`
FROM (
SELECT a.query
FROM queries a
WHERE `query` LIKE '%book%'
GROUP BY `query` , `userid`
)b
GROUP BY b.query
ORDER BY `C` DESC;

— это не то?

 

[Schumi]

Vovik - Спасибо. Сработало. Не думал о такой конструкции Но она в принципе есстественна...
baev - тоже сенкс, на будущее буим знать


Теперь по поводу оптимизации базы решил спросить. База огромна, как вижно из запросов основная выборка идет по LIKE '%book%' - значит индексы здесь не спасут.

Может у кого есть идеи, как оптимизировать структуру? Про запрос молчу

Спасибо!