|
Задавайте вопросы, мы ответим
Вы не зашли.
Нужно выбирать данные из некоторой таблицы с разными видами сортировок, привожу примеры запросов:
C cортировкой по дате:
select sql_calc_found_rows b.bandid, b.name, b.ename, b.intdate, b.comments, b.gname, b.gename
from bands b where b.status=0 order by b.dateadd desc limit 0, 30
C cортировкой по количеству:
select sql_calc_found_rows b.bandid, b.name, b.ename, b.intdate, b.comments, b.gname, b.gename
from bands b where b.bandstatus=0 order by b.comments desc limit 0, 30
C cортировкой по алфавиту:
select sql_calc_found_rows b.bandid, b.name, b.ename, b.intdate, b.comments, b.gname, b.gename
from bands b where b.bandstatus=0 order by find_in_set(b.firstchar, '9,8,7,6,5,4,3,2,1,0,z,y,x,w,v,u,t,s,r,q,p,o,n,m,l,k,j,i,h,g,f,e,d,c,b,a,я,ю,э,ь,ы,ъ,щ,ш,ч,ц,х,ф,у,т,с,р,п,о,н,м,л,к,й,и,з,ж,ё,е,д,г,в,б,а') desc, b.firstchar limit 0, 30
Запросы выполняются примерно 900-1100 мс
В таблице около 100000 записей.
Кроме выбираемых в таблице еще довольно много текстовых полей.
Индекс на bandstatus создан составные ключи на status и поля по которым сортировка не использовались.
Планы запросы для выборок с разными сортировками одинаков:
План запроса:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ref status status 1 const 52723 Using where; Using filesort
Более подробно раскрыть структуры таблицы не могу. Хотелось бы получить хотя бы общий совет:
1) Можно ли как то заставить сортироватся по составным индексам
2) Имеет ли смысл нормализация и вынесении в отдельную таблицу текстовых полей, которые тут не используются
Неактивен
Сортировка по алфавиту отвратительная, сортируйте просто по firstchar.
Список индексов таки покажите 
Скорее всего, индекса по (status, dateadd) хватит.
Неактивен
Добавил ключ на bandstatus и dateadd
Теперь запрос с сортировкой по dateadd идет меньше 100 мс что нормально.
Запрос с сортировкой по comments идет 500 мс
И с сортировкой по алфавиту более 1000 мс
Сортировка по алфавиту с использованием find_in_set обязательна по условию задачи.
План для сортировки по алфавиту:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ref status,status_dateadd status_dateadd 1 const 48483 Using where; Using filesort
Результат show keys:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
bands 0 PRIMARY 1 bandid A 78255 (null) BTREE (null)
bands 1 status 1 bandstatus A 1 (null) BTREE (null)
bands 1 dateadd 1 dateadd A 78255 (null) BTREE (null)
bands 1 firstchar 1 firstchar A 96 (null) BTREE (null)
bands 1 comments 1 comments A 1 (null) BTREE (null)
bands 1 status_dateadd 1 bandstatus A 1 (null) BTREE (null)
bands 1 status_dateadd 2 dateadd A 78255 (null) BTREE (null)
Также показывают типы данных:
Field Type Null Key Default Extra
comments int(11) NO MUL 0 (null)
dateadd int(11) NO MUL 0 (null)
bandstatus tinyint(4) NO MUL 2 (null)
firstchar char(1) NO MUL (null) (null)
Отредактированно pt81 (30.12.2009 18:56:37)
Неактивен
Я вижу что у ключа banstatus cardinality 1 в тоже время при использовании составного ключа status_dateadd используется только один символ из него.
Может ли ключ по статусу быть бесполезным.
upd: Нет убирать индекс с status не удастся все вообще тормозит и идет выборка - "ALL"
Распределение значений в столбце bandstatus:
bandstatus сnt
0 52948
1 17177
2 5824
3 51
Отредактированно pt81 (30.12.2009 19:46:54)
Неактивен
Добавил индексы по status_comments и status_firstchar
теперь план запроса с сортировкой по алфавиту такой:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ref status,status_dateadd,status_comments,status_firstchar status 1 const 55287 Using where; Using filesort
C cортировкой по comments:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ref status,status_dateadd,status_comments,status_firstchar status_comments 1 const 55287 Using where
C cортировкой по dateadd
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE b ref status,status_dateadd,status_comments,status_firstchar status_dateadd 1 const 55287 Using where
Для сортировки по comments и dateadd составной ключ видит а для сортировки по алфавиту нет.
Изменять порядок столбцов в индекс пробывал, что то вроде dateadd_status работает еще хуже.
Остаются ли какие то выходы, кроме того как облегчит все это дела, вынесением текстовый столбцов из этой таблицы?
Отредактированно pt81 (30.12.2009 20:28:12)
Неактивен
pt81 написал:
Изменять порядок столбцов в индекс пробывал, что то вроде dateadd_status работает еще хуже.
Как использовать составные ключи, в каких случаях они работают?
См. http://sqlinfo.ru/forum/viewtopic.php?id=151
pt81 написал:
Для сортировки по comments и dateadd составной ключ видит а для сортировки по алфавиту нет.
И не будет, так как вы используете find_in_set.
Если над индексируемым полем проводится какая-нибудь операция, то индекс не используется. Напрример:
SELECT .... WHERE 2*field=val; -- индекс по полю field использован не будет
SELECT .... WHERE field=val/2; -- индекс по полю field будет работать.
Вы можете создать дополнительное поле, в котором хранить результат вашего find_in_set и уже по нему делать составной индекс.
Неактивен
Варианты с отдельным полем и другими попытками избавитcя от find in set в order by я пробывал и они мне по тем или иным причинам не подошли.
Кроме того и сортивка с индексом status_dateadd по дате например, все равно использует только часть индекса - Extra
status_dateadd и работают ~300-500 мс что не мало. Думаю надо выносить в отдельную таблицу текстовые поля которые чаще всего не нужны в выборке их около 10-ти типа TEXT и VARCHAR(200) скорее всего из-за них и тормозит на 100000 записей..
Отредактированно pt81 (31.12.2009 18:33:27)
Неактивен
