[ Поиск ] - [ Пользователи ] - [ Календарь ]
Полная Версия: Оптимизация запроса
Форум PHP программистов > PHP + SQL
kirik
21.06.2009 - 00:28
Приветы!

Ребят, скажите какой выход видите в такой ситуации:
есть таблица (30к записей), в таблице есть поля "deleted" и "approved". Когда выводим посты обычным юзерам пост должен быть не удален и зааппрувлен, запрос что-то типа:
SQL
SELECT ... FROM `posts` WHERE `deleted` = 0 AND `approved` = 1


Есть составной индекс на deleted и approved. При таком запросе отсеиваются всего пара сотен записей, но все хорошо, пока в запросе не принимает участия сортировка..

Запрос
SQL
SELECT SQL_NO_CACHE *
FROM `posts` WHERE `approved` = 1 AND `deleted` = 0
LIMIT 0, 10;

занимает около 0.0005 с, а запрос
SQL
SELECT SQL_NO_CACHE *
FROM `posts` WHERE `approved` = 1 AND `deleted` = 0
ORDER BY `post_id` DESC LIMIT 0, 10;

уже 0.2300 с. Хотя сортировка происходит по уникальному полю - post_id..

Что делать, куда звонить? smile.gif
Спасибы!



Спустя 6 минут, 16 секунд (20.06.2009 - 23:34) Alchemist написал(а):
а что EXPLAIN говорит ?

Спустя 3 минуты (20.06.2009 - 23:37) HardWoman написал(а):
А сколько всего значений может принимать deleted и approved ?

Если каждый 0 и 1, то может имеет смысл сделать один столбец и 4 значения?
00, 01,11, 10 (1,2,3,4) тогда условие будет одно?

А индексировать поля по составному индексу при ограниченном количестве вариантов - это как если индексировать по полу М ищет М М ищет женщину итд - мне так видится - не принесет результатов. Что есть индекс, что его нет. Почти одно и тоже?

Может что то не то сказала - прошу пардону. smile.gif
Считайте это вопросом. Мне бы интересно было узнать, что скажете?

Спустя 10 минут, 9 секунд (20.06.2009 - 23:47) kirik написал(а):
Цитата (Alchemist @ 20.06.2009 - 15:34)
а что EXPLAIN говорит ?


SQL
EXPLAIN SELECT SQL_NO_CACHE *
FROM `posts` WHERE `approved` = 1 AND `deleted` = 0
LIMIT 0, 10

выводит
Код
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  posts  ref  approved  approved  2  const,const  30280


SQL
EXPLAIN SELECT SQL_NO_CACHE *
FROM `posts` WHERE `approved` =1 AND `deleted` =0
ORDER BY `post_id`
LIMIT 0, 10

дает
Код
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  posts  ref  approved  approved  2  const,const  30245  Using where; Using filesort

Спустя 5 минут, 20 секунд (20.06.2009 - 23:53) kirik написал(а):
Цитата (HardWoman @ 20.06.2009 - 15:37)
А сколько всего значений может принимать deleted и approved ?

Если каждый 0 и 1, то может имеет смысл сделать один столбец и 4 значения?
00, 01,11, 10 (1,2,3,4) тогда условие будет одно?

Да, всего 2 варианта - 0 и 1. Но тут похоже дело не в количестве полей, потому как
SQL
SELECT SQL_NO_CACHE *
FROM `posts` WHERE `approved` = 1
ORDER BY `post_id`
LIMIT 0, 10

тоже занимает 0.2711 секунды..

Спустя 3 минуты, 21 секунда (20.06.2009 - 23:56) glock18 написал(а):
SQL
EXPLAIN SELECT SQL_NO_CACHE *
FROM `posts` WHERE `approved` = 1 AND `deleted` = 0
ORDER BY `post_id`
LIMIT 0, 10


mysql, если не ошибаюсь, может применить только один индекс на запросе. В обоих случаях mysql считает нужным использовать approved (вероятно им отсеивается наибольшее количество записей). остальные записи проходятся без использования индекса, а так 1ый запрос берет просто первые 10 попавшихся, то 2ой должен выбрать все - отобрать из низ 10 самых "post_id'шных" и вывести.

по-моему, что-то такое получается.

я уже не помню, но, возможно, удастся добиться улучшения добавление в index по approved поля post_id. чтобы точно быть уверенным лучше почитай про оптимизацию order by и where:

http://dev.mysql.com/doc/refman/5.0/en/ord...timization.html
http://dev.mysql.com/doc/refman/5.0/en/whe...imizations.html

PS: поправлюсь.
не
Цитата
может применить только один индекс на запросе

а... ну... таблице что ли smile.gif ну ты понял. на join'ах тоже может, вроде использовать index в отдельности. на вложенных запросах, понятно...

Спустя 2 минуты, 29 секунд (20.06.2009 - 23:58) HardWoman написал(а):
А если попробовать сделать составной индекс с полями post_id и deleted ? тогда в условии будет присутствовать уникальный индекс?
А если еще объеденить эти два столбца, как я говорила выше

Спустя 1 минута, 11 секунд (21.06.2009 - 00:00) Alchemist написал(а):
Цитата
1  SIMPLE  posts  ref  approved  approved  2  const,const  30245  Using where; Using filesort


какой нафик filesort ?!!! при сортировке по одному параметру, да еще являющемуся PRIMARY KEY никакого файлсорта быть не должно !

попробуй сделать OPTIMIZE TABLE, а потом ANALYZE TABLE. Если не поможет, сбрось индексы, выставь по новой, и опять оптимизируй.

ЗЫ: О, вот что я еще подумал: убери составной индекс с полей и сделай два раздельных.

Спустя 2 минуты, 47 секунд (21.06.2009 - 00:02) glock18 написал(а):
Цитата (Alchemist @ 20.06.2009 - 21:00)
какой нафик filesort ?!!! при сортировке по одному параметру, да еще являющемуся PRIMARY KEY никакого файлсорта быть не должно !


в том то и дело, что mysql похоже вовсе не primary key использует. что по меньшей мере странно.

Спустя 57 секунд (21.06.2009 - 00:03) kirik написал(а):
Цитата (Alchemist @ 20.06.2009 - 16:00)
попробуй сделать OPTIMIZE TABLE, а потом ANALYZE TABLE. Если не поможет, сбрось индексы, выставь по новой, и опять оптимизируй.


Сделал OPTIMIZE TABLE и ANALYZE TABLE:
Код
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  links_posts  ref  approved  approved  1  const  30675  Using where; Using filesort


Сейчас попробую переставить индексы..

Спустя 1 минута, 5 секунд (21.06.2009 - 00:04) kirik написал(а):
Цитата (Alchemist @ 20.06.2009 - 16:00)
ЗЫ: О, вот что я еще подумал: убери составной индекс с полей и сделай два раздельных.


SQL
EXPLAIN SELECT SQL_NO_CACHE *
FROM `posts` WHERE `approved` =1 AND `deleted` =0
ORDER BY `post_id`
LIMIT 0, 10

так же filesort:
Код
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  links_posts  ref  approved,deleted  deleted  1  const  30368  Using where; Using filesort

Спустя 3 минуты, 21 секунда (21.06.2009 - 00:08) Alchemist написал(а):
какая тусовка smile.gif

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

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

Спустя 16 секунд (21.06.2009 - 00:08) glock18 написал(а):
Цитата (kirik @ 20.06.2009 - 21:04)
1SIMPLElinks_postsrefapproved,deleteddeleted1const30368Using where; Using filesort


другой индекс применил. а что если извернуться так:

SQL
ORDER BY `post_id`, `approved` DESC, `deleted`;


тогда ну по-любому должен по post_id индекс использовать. иного варианта просто не будет.

Спустя 1 минута, 8 секунд (21.06.2009 - 00:09) Alchemist написал(а):
kirik, кинь сюда дамп таблички, поиграюсь у себя...

glock18, если сортировка больше чем по одному полю - filesort неизбежен.

Спустя 4 минуты, 7 секунд (21.06.2009 - 00:13) glock18 написал(а):
Цитата (Alchemist @ 20.06.2009 - 21:09)
glock18, если сортировка больше чем по одному полю - filesort неизбежен.


ааа... ну... эт запрос еще и работать не так будет biggrin.gif я только я щас че-т заметил.

Спустя 4 минуты, 7 секунд (21.06.2009 - 00:17) HardWoman написал(а):
Цитата
Когда выводим посты обычным юзерам пост должен быть не удален и зааппрувлен

Ключевое слово ОБЫЧНЫМ

Мне вообще тогда не понятен смысл этих полей. Для других групп пользователей топик может быть доступен на удаление?

Это же вопрос вывода ссылок на допустимые действия? Не знаю как это правильно делается, но может в сессию заносить статус пользователя и тогда обысному юзеру просто делать другой вывод без ссылок на удаление?

Спустя 55 секунд (21.06.2009 - 00:18) kirik написал(а):
Цитата (Alchemist @ 20.06.2009 - 16:09)
кинь сюда дамп таблички, поиграюсь у себя...


Сори, дамп весь не могу дать, а структура -
Свернутый текст
SQL
CREATE TABLE `posts` (
`post_id` mediumint(8) unsigned NOT NULL auto_increment,
`original_id` mediumint(8) unsigned NOT NULL default '0',
`author` mediumint(8) unsigned NOT NULL,
`title` varchar(100) NOT NULL,
`category` varchar(50) NOT NULL,
`genre` varchar(255) NOT NULL,
`description` text NOT NULL,
`release` smallint(4) unsigned NOT NULL,
`studio` varchar(50) NOT NULL,
`director` varchar(50) NOT NULL,
`starring` text NOT NULL,
`other` text NOT NULL,
`password` varchar(100) NOT NULL,
`front_cover` tinyint(1) unsigned NOT NULL default '0',
`screenshot` tinyint(1) unsigned NOT NULL default '0',
`back_cover` tinyint(1) unsigned NOT NULL default '0',
`views` mediumint(8) unsigned NOT NULL default '0',
`rate_num` mediumint(8) unsigned NOT NULL default '0',
`rate_sum` mediumint(8) unsigned NOT NULL default '0',
`comments` smallint(5) unsigned NOT NULL default '0',
`approved` tinyint(1) unsigned NOT NULL default '0',
`deleted` tinyint(1) unsigned NOT NULL default '0',
`on_top` tinyint(1) unsigned NOT NULL default '0',
`time_add` int(10) unsigned NOT NULL,
`edit_date` int(10) unsigned NOT NULL default '0',
`editor_id` mediumint(8) unsigned NOT NULL default '0',
`backup` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`post_id`),
KEY `on_top` (`on_top`),
KEY `category` (`category`),
KEY `title` (`title`(20)),
KEY `approved` (`approved`),
KEY `deleted` (`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Спустя 1 минута, 39 секунд (21.06.2009 - 00:20) kirik написал(а):
Цитата (HardWoman @ 20.06.2009 - 16:17)
Для других групп пользователей топик может быть доступен на удаление?

Не, это статус поста - поле approved- одобрен-ли пост модератором, deleted- удален-ли пост пользователем (чтобы модератор мог его восстановить)..

Спустя 9 минут, 25 секунд (21.06.2009 - 00:29) Alchemist написал(а):
хех, а чего мне структура ? smile.gif я запросы погонять хотел smile.gif

забей критичные поля мусором, да скинь по аське, если боишься smile.gif 127722876

Спустя 1 минута, 14 секунд (21.06.2009 - 00:31) HardWoman написал(а):
А почему бы не сделать отдельную таблицу? На удаленные топики? А в основной такие делать статус скрывать. А после подтверждения модератором удаления удалять в основной таблице id топа?

Спустя 25 секунд (21.06.2009 - 00:31) kirik написал(а):
Я только что вообще убил оба индекса ради интереса.. Выполнил запрос
SQL
EXPLAIN SELECT SQL_NO_CACHE *
FROM `posts` WHERE `approved` =1 AND `deleted` =0
ORDER BY `post_id`
LIMIT 0, 10

получил
Код
1   SIMPLE   posts   index   NULL   PRIMARY   3   NULL   30696   Using where

И выполняется теперь он за 0.0005 секунды unsure.gif

Выходит что в этом случае индексы только мешают?

Спустя 1 минута, 28 секунд (21.06.2009 - 00:32) kirik написал(а):
Цитата (HardWoman @ 20.06.2009 - 16:31)
А почему бы не сделать отдельную таблицу? На удаленные топики?

Не практично выходит.. Да и дописывать движок не хочется smile.gif

Спустя 37 секунд (21.06.2009 - 00:33) Alchemist написал(а):
ну собсно я хотел это первым номером проверить smile.gif) не хотел писать так как не был уверен smile.gif

Спустя 5 минут, 21 секунда (21.06.2009 - 00:38) kirik написал(а):
Цитата (Alchemist @ 20.06.2009 - 16:33)
ну собсно я хотел это первым номером проверить

Вот ерунда... Получается что поля, которые не могут исключить сразу много строк вообще не стоит в индекс вносить?..

Спустя 1 минута, 27 секунд (21.06.2009 - 00:40) HardWoman написал(а):
Видимо так и есть скрипт проиндексировал по первому беспонтовому индексу, и видимо последующая сортировка по post_id занимает больше времени

Спустя 1 минута, 33 секунды (21.06.2009 - 00:41) HardWoman написал(а):
Цитата
Вот ерунда... Получается что поля, которые не могут исключить сразу много строк вообще не стоит в индекс вносить?..


Это в первых строках по оптимизации запросов пишут. такие индексы беспонтовые и как теперь видно и вредные

Спустя 4 минуты, 29 секунд (21.06.2009 - 00:46) Alchemist написал(а):
Цитата (kirik @ 20.06.2009 - 23:31)
Выходит что в этом случае индексы только мешают?


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

Когда ты убрал дополнительные индексы, мускуль не нашел подходящих для выборки, поэтому в действие вступила ORDER BY оптимизация. Мускуль начал выборку сразу по требуемому порядку (в данном случае - primary index), просто проверяя каждую строку на соответствие условию WHERE. Как только набралось 10 - остановился и выдал ответ.

Раньше же происходило следущее: поскольку был индекс на одно из поисковых полей, то разумеется умный мускуль использовал этот индекс, наивно полагая что юзер знает что делает smile.gif. В результате, он уже не мог выбирать строки в том порядке который требовался в запросе, и вынужден был:
1) сначала выбирать все строки отвечающие условию. (> 30k)
2) (по видимому) величина полученой таблицы была слишком велика для оперативного кэша, так что приходилось использовать filesort.

Спустя 4 минуты, 6 секунд (21.06.2009 - 00:50) kirik написал(а):
Цитата (HardWoman @ 20.06.2009 - 16:41)
такие индексы беспонтовые и как теперь видно и вредные

Теперь буду знать! smile.gif Я и так не особо злоупотреблял индексами..

Alchemist
Спасибо за объяснение! Понял свою ошибку smile.gif


Ребятки, спасибо вам всем большое! Мне этот запрос уже сниться начинал smile.gif
Спасибо!

Спустя 35 секунд (21.06.2009 - 00:51) HardWoman написал(а):
Интересная штука этот php. Получила истинное наслаждение читая этот топ smile.gif
Быстрый ответ:
Powered by dgreen

 Графические смайлики |  Показывать подпись
Здесь расположена полная версия этой страницы.
Invision Power Board © 2001-2025 Invision Power Services, Inc.