Форум пользователей MySQL

paulus написал:

Какая жуть

1. Насколько я вижу, b1 тождественно равно b в этом запросе => -1 таблица.

Убрал b1.
На скорость никак не повлияло, или малозаметно. Хотя она и правда лишняя.

paulus написал:

2. Время выполнения не такое уж и плохое для такого запроса, Вы просто хотите
избавиться от страшных слов, или боитесь, что упретесь в производительность
на 10 миллионах карточек?

Размер запроса  и страшные слова не волнуют - главное скорость!

paulus написал:

3. Боюсь, если Вы хотите сделать это быстрее - прийдется перепродумать структуру.
Кусок типа "189 in (...)" никогда не будет использовать индекс.

По-моему они все таки используются (обратите внимание на eq_ref):
id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    b    eq_ref    PRIMARY    PRIMARY    4    test.l.id_business    1   
1    SIMPLE    b2    eq_ref    PRIMARY    PRIMARY    4    test.b.id_parent    1   
1    SIMPLE    b3    eq_ref    PRIMARY    PRIMARY    4    test.b2.id_parent    1   
1    SIMPLE    b4    eq_ref    PRIMARY    PRIMARY    4    test.b3.id_parent    1    Using where


Перенес позиции таблиц в запросе - ug на 1е место, и создал индекс для с (id_organization, id)
SELECT
    c.id card,
    b.name b_name,
    b.document_url b_url,
    ug.id_package,
    o.id,
    o.title,
    o.description,
    o.telephone,
    o.publish_site,
    o.date_create,
    o.city adress,
    s.domain site
FROM `usage_groups` ug   
    JOIN `organizations` o ON ug.`id_organization` = o.id
    JOIN `cards` c ON ug.`id_organization` = c.`id_organization`
    JOIN links l ON o.id = l.`id_organization`
    JOIN `usage` u ON l.`id_usage` = u.`id`
    JOIN `businesses` b ON b.id = l.id_business
    JOIN `sites` s ON o.id = s.id_organization
    LEFT JOIN `businesses` b2 ON b2.`id` = b.`id_parent`
    LEFT JOIN `businesses` b3 ON b3.`id` = b2.`id_parent`
    LEFT JOIN `businesses` b4 ON b4.`id` = b3.`id_parent`
WHERE o.publish = 'Y'
     AND 189 IN (
                l.`id_business`,
                b.`id_parent`,
                b2.`id_parent`,
                b3.`id_parent`,
                b4.`id_parent`
                )                            
GROUP BY ug.`id_organization`
ORDER BY ug.id_package DESC, o.date_create
LIMIT 100, 10

EXPLAIN стал лучше - вместо 106000 перебирается 1200 - используется меньшая таблица ug:
id    select_type    table    type    key    key_len    ref    rows    Extra
1    SIMPLE    o    ref        publish    4    const    1290    Using where; Using temporary; Using filesort
1    SIMPLE    ug    ref        FK_usage_group_organiuzation    4    test.o.id    1   
1    SIMPLE    l    ref        FK_links_organizations_id    4    test.o.id    1   
1    SIMPLE    b    eq_ref        PRIMARY    4    test.l.id_business    1   
1    SIMPLE    b2    eq_ref        PRIMARY    4    test.b.id_parent    1   
1    SIMPLE    b3    eq_ref        PRIMARY    4    test.b2.id_parent    1   
1    SIMPLE    b4    eq_ref        PRIMARY    4    test.b3.id_parent    1    Using where
1    SIMPLE    s    ref        FK_sites_organization    5    test.l.id_organization    1    Using where
1    SIMPLE    u    eq_ref        PRIMARY    4    test.l.id_usage    1    Using index
1    SIMPLE    c    ref        FK_cards_organization    5    test.ug.id_organization    7    Using where; Using index

Время выполнения уменьшилось на 0.05 сек, но filesort остался и все равно медленно?

Можно ли еще ускорить?

paulus написал:

eq_ref - это хороший способ составления таблиц в JOIN. Он имеет отношение
к JOIN `usage` u ON l.`id_usage` = u.`id`, например. Но он не имеет отношения
к фильтрации данных, которые есть 189 in (...) и вследствие которого есть
"using where" справа от нескольких колонок.

Попробуйте переписать его в виде
WHERE b.id_parent = 189 OR ...

При этом после добавления на b ключика на (id_parent, id) должно летать.

Вы будете удивлены, но использование:
WHERE o.publish = 'Y'
     AND (
                          l.`id_business` = 189
        OR b.`id_parent` = 189
        OR b2.`id_parent` = 189
        OR b3.`id_parent` = 189
        OR b4.`id_parent` = 189
        )

никак не повлияло на скорость, а ключик на b (id_parent, id) никак не повлиял на скорость и даже никак не изменил EXPLAIN.
Хотя в теории я с вами согласен. Может быть новая версия MySQL стала использовать индексы на IN выражениях..

0.16 сек предел на свободном сервере, а на рабочем 1.00 - 5.00 сек

Есть ли смысл оптимизировать дальше? Или копаться в настройках сервера MySQL ?

Оптимизатор решил, что INDEX_MERGE в данном случае будет хуже FTS. Возможно,
он прав

Можете сделать табличку "предки бизнеса", где держать всех предков каждого
бизнеса (не только прямых родителей). Тогда будет один INNER JOIN, который
точно пробивает по индексу.

P.S. MySQL умеет использовать индексы в случае с IN, когда Вы пишете
column IN (value list), но не в обратном случае.

Еще интересный факт - чистая выборка всех в данном дереве занимает 0.02 сек. Т.е. надо наверное выдвинуть ее наперед.
SELECT   
    b.name b_name
FROM
    `businesses` b
    LEFT JOIN `businesses` b2 ON b2.`id` = b.`id_parent`
    LEFT JOIN `businesses` b3 ON b3.`id` = b2.`id_parent`
    LEFT JOIN `businesses` b4 ON b4.`id` = b3.`id_parent`
WHERE  (
         b.id = 189
        OR b.`id_parent` = 189
        OR b2.`id_parent` = 189
        OR b3.`id_parent` = 189
        OR b4.`id_parent` = 189
        )
LIMIT 100, 10
10 rows fetched (0,02 sec)

Я плохо знаю как пользоваться STRAIGHT_JOIN Буду пробовать..

paulus написал:

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

Выигрыш 0.04 секунды на лицо - спасибо.
Наверное это предел 0.11 сек для такого громоздкого запроса. Или еще остались способы?

paulus написал:

Способ - переделать сами данные. Сделайте таблицу, все-таки, с предками. Один JOIN
будет работать с индексом при ограничении WHERE, сейчас у Вас все равно выбираются
все строки, а потом идет поиск по этой временной табличке.

Это я к сожалению сделать не смогу - не в моей компетенции изменение структуры таблиц. С деревом завязано много другого.
STRAIGHT_JOIN - отличная штука!

paulus написал:

Тогда посмотрите еще на значение tmp_table_size. Если оно достаточно большое,
то временные таблицы будут создаваться в памяти, что несколько ускорит Ваши
запросы (я имею в виду production-машинку).

Всего на машинке 512М, а tmp_table_size=32М. Есть ли смысл увеличивать?