Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > постоянный filesort при SELECT age BETWEEN x AND y

Автор: shurale 31.8.2006, 01:16

Есть таблица - 100 000 записей, MYSQL 4.0.24
Делается запрос -
SELECT id,..... FROM users WHERE area='xxx' AND phone_preff='xxx' AND age BETWEEN x AND y ORDER BY timeregistered DESC LIMIT 0,10

Стоит key (area,phone_preff,age,timeregistered)
Поля - area - ENUM(1,2,3,....) phone_preff - smallint(3),  age -  smallint(3), timeregistered - int(10)

Проблема в том, что при запросе с  age BETWEEN ... Mysql не использует ключ и в результате - при запросе EXPLAIN - using where, using filesort

Тогда как при изменении условия age BETWEEN x AND y на age=x все нормализуется и filesort исчезает, остается только  using where

При интенсивной работе базы нагрузка очень хорошо ощущается, пытался обойти каким либо образом, ни чего не помогает - вот варианты,
вместо BETWEEN x AND y -
age IN ('x','x1','x2',...)
еще обычный - age > x and age<y
Все равно - filesort!

а так же пытался использовать UNION -

Код


(SELECT id,..... FROM users WHERE area='xxx' AND phone_preff='xxx' AND age = 20)
UNION
(SELECT id,..... FROM users WHERE area='xxx' AND phone_preff='xxx' AND age = 21)
UNION
(SELECT id,..... FROM users WHERE area='xxx' AND phone_preff='xxx' AND age = 22)
.
.
.
ORDER BY timeregistered DESC LIMIT 0,10

Однако при большом возрастном диапозоне этот запрос еще тяжелее предыдущихб хотя и не использует filesort

Есть какое то более интересное и быстрое решение, оптимизировать запрос?

Автор: skyboy 31.8.2006, 10:09

если бы ты сбросил структуру и маленький(на 8-10 строк - можно прикрепить файлом к сообщению) дамп таблицы, можно было бы проэкспериментировать и быстрее найти решение. А касательно UNION - так у тебя запрос перед выдачей результата делает поиск, чтоб не было одинаковых строк. Если такого быть наверняка не может, то делай UNION ALL. впрочем, всё равно будет медленно, просто имей в виду на будущее.

Автор: Kesh 31.8.2006, 10:35

Точно... Давай дамп... на 5-ке поэкспериментирую...

Автор: Akina 31.8.2006, 10:44

А ты не пробовал явно указать что использовать при выполнении запроса? USE INDEX/IGNORE INDEX

И еще:

Цитата

При помощи команды EXPLAIN SELECT ... ORDER BY можно проверить, может ли MySQL использовать индексы для выполнения запроса. Если в столбце extra содержится значение Using filesort, то MySQL не может использовать индексы для выполнения сортировки ORDER BY.

Методы пассивной борьбы ищи также в мане по MySQL... может завести отдельный индекс по age?

Автор: shurale 31.8.2006, 11:10

Спасибо всем за ответы.

Akina, да, пробовал использовать USE INDEX(search)

когда search - (area,phone_preff,age,timeregistered)
Не помогает!

Kesh,
skyboy, Спасибо за совет с UNION ALL.

Вот кусок таблицы со структурой -

Код


CREATE TABLE `test` (
  `id` mediumint(10) NOT NULL auto_increment,
  `area` enum('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15') NOT NULL default '1',
  `phone_preff` smallint(3) NOT NULL default '0',
  `age` smallint(3) NOT NULL default '0',
  `name` varchar(100) NOT NULL default '',
  `timeregistered` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `search` (`area`,`phone_preff`,`age`,`timeregistered`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

-- 
-- Dumping data for table `test`
-- 

INSERT INTO `test` VALUES (1, '7', 67, 34, 'Vasia', 1111947086);
INSERT INTO `test` VALUES (2, '4', 543, 63, 'Igor', 1111941086);
INSERT INTO `test` VALUES (3, '3', 982, 23, 'Test', 1111947881);
INSERT INTO `test` VALUES (4, '5', 123, 19, 'test2', 1111941024);
INSERT INTO `test` VALUES (5, '6', 124, 42, 'test3', 1121947086);
INSERT INTO `test` VALUES (6, '6', 21, 48, 'test4', 1111942568);
INSERT INTO `test` VALUES (7, '5', 673, 22, 'test5', 1111947123);

Интересно, если можно придумать обходной путь для выборки по возрасту.... ,
да - хостинг не позволяет использовать 5 мускул

Добавлено @ 11:11
Akina, пробовал завести отдельный индекс по возрасту - ноль результата - не используется

Автор: shurale 31.8.2006, 11:56

Походу дела решение нашлось -
Создаем доп. ключ area (area,phone_preff,timeregistered)

И при запросе

Код


 SELECT * FROM test USE INDEX ( area ) WHERE area = '3' AND phone_preff = '546' AND age BETWEEN 2 AND 100 ORDER BY timeregistered DESC LIMIT 0 , 10

Уже не используется filesort !!

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

Автор: Akina 31.8.2006, 11:56

Не, если завести и сказать USE INDEX age - все одно filesearch?

Автор: shurale 31.8.2006, 11:58

Akina, да, все одно, не использует...

Автор: Secandr 31.8.2006, 16:43

офтопик: читая эту тему проабгрейдил один из своих скриптов. Спасибо!

Автор: muzer 31.8.2006, 21:24

Вам дали почти правильный совет по поводу использования конструкции USE INDEX. Но USE носит рекомендательный характер для mysql. Есть более "сильный" вариант: FORCE INDEX () (работает с 4.0.9 версии), mysql будет использовать данный в скобках индекс, если это возможно для выполнения запроса.

А по поводу того, почему игнорируется индекс при задании рэнджа для age:
При выборе способа отработки селект-запроса MySQL решает, целеобразно ли ему использовать какой-либо индекс. По моим наблюдениям, отрицательное решение принимается в случае, когда кол-во результирующих строк будет больше десятка процентов от кол-ва записей в индексе. Т.е. в вашем случае для age индекс используется только тогда, когда ему задают конкретное значение (скорее всего, если написать age between 25 and 26, индекс всё-таки тоже будет использоваться, т.к. такой рэндж охватывает относительно малое кол-во строк).
Почему MySQL не решает использовать только первую часть индекса - загадка природы разработчиков этой БД smile

Автор: Ignat 2.9.2006, 09:18

Цитата(muzer @ 31.8.2006, 22:24

)

Почему MySQL не решает использовать только первую часть индекса

По моим наблюдениям, мускуль использует либо индекс целиком, либо вообще не использует.

Кстати, у меня была подобная проблема, но наличие filesort'а там было критично ( ndbcluster не дружит с оным ), посему было решено забить на сортировку и сделать её на клиенте.

Цитата(muzer @ 31.8.2006, 22:24

)

Есть более "сильный" вариант: FORCE INDEX () (работает с 4.0.9 версии),

За это спасибо smile

Автор: muzer 3.9.2006, 14:45

Цитата(Ignat @ 2.9.2006, 10:18

)

По моим наблюдениям, мускуль использует либо индекс целиком, либо вообще не использует.

Нет, ну почему же, левую часть индекса он умеет использовать:

Код


CREATE TABLE `Test` (
  `id1` int(11) default NULL,
  `id2` int(11) default NULL,
  KEY `id1` (`id1`,`id2`)
) 

INSERT.. порядка 60 тыс полурэндомных строк

mysql> EXPLAIN SELECT * FROM Test WHERE ID1 BETWEEN 10000 AND 20000 AND ID2 BETWEEN 1000 AND 1500;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Test  | range | id1           | id1  | 10      | NULL | 9544 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

mysql> EXPLAIN SELECT * FROM Test WHERE ID1 BETWEEN 10000 AND 20000;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Test  | range | id1           | id1  | 5       | NULL | 9545 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

Обратите внимание на key_len, в первом случае весь индекс (2 инта), во втором - только левая часть.

Автор: S.A.P. 3.9.2006, 14:50

shurale, попробуй еще вот такой запрос

Код


SELECT *
FROM 
(
    SELECT id,..... 
    FROM users 
    WHERE area='xxx' AND phone_preff='xxx' AND age BETWEEN x AND y
) AS t
ORDER BY timeregistered DESC 
LIMIT 0,10

возможно мускул пытается отсортировать сначала всю таблицу, а потом сделать WHERE, потом LIMIT.

Автор: shurale 3.9.2006, 15:44

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

Автор: Ignat 4.9.2006, 09:09

Цитата(S.A.P. @ 3.9.2006, 15:50

)

возможно мускул пытается отсортировать сначала всю таблицу, а потом сделать WHERE, потом LIMIT.

Нет, это он не может делать...

Цитата(muzer @ 3.9.2006, 15:45

)

Обратите внимание на key_len, в первом случае весь индекс (2 инта), во втором - только левая часть.

Действительно еще одна загадка smile

Использовать или не использовать выбираеся оптимизатором по среднепотолочной системе?

Автор: S.A.P. 4.9.2006, 10:50

Цитата(Ignat @ 4.9.2006, 09:09

)

Нет, это он не может делать...

как бы то ни было это решение меня спасало от filesorta в аналогичных запросах.