Оптимизация запроса

[dmsoh]

а мне кажется, что тут проблема:
GROUP BY
ORDER BY

что explain каже?

сделал скриншот.

 

[dmsoh]

Тогда нужно определить, где главные тормоза: при выборке, джойне, группировке или сортировке.
Сначала сделать SELECT a.id FROM ..... чтобы понять, что это тормозят не вычисления в селекте.
Потом по очереди убирать джойны, группировки, сортировки и смотреть, где будет скачок производительности. Т.е. убрать последний джойн, потом группировку, потом сортировку; убрать предпоследний джойн и т.д.

Когда время резко скакнет, будет видно какая таблица тормозит.

Понял. Попробую. Напишу о результате.

 

[zerdek]

Хороший выигрыш можно получить - частично отказавшись от нормализации данных.

Малой кровью, на уже запущенном проекте - добавить в основную таблицу доп.поля.
т.е. к примеру в таблице __autobb_messages есть поле city - добавляем поле city_title. Его значение берем соотвественно из таблички autobb_cities (один раз апдейт существующей базы и ставим заполнение этого поля при добавлении новой записи). после этого убираем нафиг джойн этой таблицы. По максимуму избавляемся от всех джойнов.
Итого: немного разрастется база, но увеличиться быстродействие, возможно, в несколько сотен раз.

мелочи: лишние поля из select-a тож **х. нужны ли все значения a.*?

 

[liquinoid]

Сделай кеширование и не парься!

Если есть доступ к самому серверу бд - настрой кеширование.

Ну или же сохраняй где-нибудь результат селектов (файлы/память/опять же база) и потом по быстрому отдавай. Ну и TTL выставиь адекватный. Посмотри на Zend_Cache из Zend_Framework'a, может почерпнешь идей новых.

 

[dmsoh]

Хороший выигрыш можно получить - частично отказавшись от нормализации данных.

Малой кровью, на уже запущенном проекте - добавить в основную таблицу доп.поля.
т.е. к примеру в таблице __autobb_messages есть поле city - добавляем поле city_title. Его значение берем соотвественно из таблички autobb_cities (один раз апдейт существующей базы и ставим заполнение этого поля при добавлении новой записи). после этого убираем нафиг джойн этой таблицы. По максимуму избавляемся от всех джойнов.
Итого: немного разрастется база, но увеличиться быстродействие, возможно, в несколько сотен раз.

мелочи: лишние поля из select-a тож **х. нужны ли все значения a.*?

Ну вообщем-то избавился от 4 таблиц: вот для сравнение то что было:

SELECT a.*, a.mileage*(1 + a.mileage_unit*0.609) as metric_mileage, a.mileage*(1 - (1 - a.mileage_unit)*0.3785) as english_mileage,
 p.id as photo_id, count(p.id) as photo_count, v.itemid as vendor_itemid, v.title as vendortitle, m.itemid as model_itemid, m.title as modeltitle, c.title as colortitle,
 cur.sign as currency_sign, a.price*cur.rate as price_in_rur, DATE_FORMAT(a.createDate, '%a, %d %b %Y %T GMT') as rfcDate, ct.title as city_title, r.title as region_title
 FROM jos_autobb_messages AS a
 LEFT JOIN jos_autobb_photos AS p
 ON a.id=p.msgid
 LEFT JOIN jos_autobb_vendors AS v
 ON a.vendor=v.id
 LEFT JOIN jos_autobb_models AS m
 ON a.model=m.id
 LEFT JOIN jos_autobb_colors AS c
 ON a.color=c.id
 LEFT JOIN jos_autobb_currency AS cur
 ON a.currency=cur.id
 LEFT JOIN jos_autobb_cities AS ct
 ON a.city=ct.id
 LEFT JOIN jos_autobb_regions AS r
 ON ct.region=r.id
 WHERE a.expirationDate>=NOW() AND a.published=1
 GROUP BY a.id
 ORDER BY a.sticked DESC, a.ordering ASC, a.CreateDate DESC
 LIMIT 0, 10

а сейчас вот так:

SELECT a.*, a.mileage as metric_mileage,
  v.itemid as vendor_itemid, v.title as vendortitle, m.itemid as model_itemid, m.title as modeltitle,
  a.price as price_in_rur, DATE_FORMAT(a.createDate, '%a, %d %b %Y %T GMT') as rfcDate, ct.title as city_title
 FROM jos_autobb_messages AS a
 LEFT JOIN jos_autobb_vendors AS v
 ON a.vendor=v.id
 LEFT JOIN jos_autobb_models AS m
 ON a.model=m.id
 LEFT JOIN jos_autobb_cities AS ct
 ON a.city=ct.id
 WHERE a.expirationDate>=NOW() AND a.published=1
 GROUP BY a.id
 ORDER BY a.sticked DESC, a.ordering ASC, a.CreateDate DESC
 LIMIT 0, 10

Производительность конечно увеличилась в 2 раза. Но все же запрос очень долго выполняется.

 

[Simpson]

предлагаю вместо одного сложного запроса сделать четыре простых и как-то избавиться от GROUP BY

 

[zerdek]

Производительность конечно увеличилась в 2 раза. Но все же запрос очень долго выполняется.

бегло глянул, соответствие на линкованных таблицах вроде однозначное.
по идее group by a.id можно убрать. для проверки посмотрите так ли это - добавить в запрос поле count(*). если везде значение 1, то групировку нафиг.

по идее уже сейчас этот запрос должен просто летать...
какие сейчас индексы на таблицах?
самому mysql-ю памяти для работы хватает?

 

[dmsoh]

предлагаю вместо одного сложного запроса сделать четыре простых и как-то избавиться от GROUP BY

бегло глянул, соответствие на линкованных таблицах вроде однозначное.
по идее group by a.id можно убрать. для проверки посмотрите так ли это - добавить в запрос поле count(*). если везде значение 1, то групировку нафиг.

по идее уже сейчас этот запрос должен просто летать...
какие сейчас индексы на таблицах?
самому mysql-ю памяти для работы хватает?

Ребята, спасибочки вам!

Убрал group by a.id, вот что получил:

Отображает строки 0 - 9 (10 всего, запрос занял 0.0404 сек.)

А с ним:

Отображает строки 0 - 9 (10 всего, запрос занял 2.7108 сек.)

Собственно вы мне очень помогли. Сайт будет жить)

Ну и напоследок, а индексы сильно влияют на производительность?
Как их можно посмотреть?

 

[zerdek]

Ну и напоследок, а индексы сильно влияют на производительность?

Сейчас на таблице jos_autobb_messages индексы expirationDate, published

индексы могут сильно влиять. по всем таблицам нужен PRIMARY KEY (`id`)
по jos_autobb_messages желательно еще добавить составной индекс
по трем полям: sticked, ordering, CreateDate. именно в таком же порядке.


Добавлено через 6 минут

Ну и напоследок, а индексы сильно влияют на производительность?
Как их можно посмотреть?

посмотреть или через пхпадмин или в sql дампе. из того, что выложено на форуме я вижу такие индексы в jos_autobb_messages

...
  PRIMARY KEY  (`id`),
  KEY `createDate` (`createDate`),
  KEY `expirationDate` (`expirationDate`),
  KEY `vendor` (`vendor`),
  KEY `model` (`model`),
  KEY `city` (`city`),
  KEY `state` (`state`)

не увидел тут индекса по published

 

[Simpson]

Убрал group by a.id, вот что получил:
Отображает строки 0 - 9 (10 всего, запрос занял 0.0404 сек.)

да, там в эксплейне видно using temporary. а про разделение запроса это для избаления от джойнов, которые пугали PHP_Master-а