Поддержка Проблемы и решения Оптимизация sql запроса с join

  • Доброе утро. Облазив просторы интернета примерно до 7 утра, так и не смог найти оптимального решения выполнения задачи, решил обратиться к вам коллеги, может что посоветуете.

    Имеем такой запрос

    SELECT wp_posts.*
    FROM wp_posts
                    INNER JOIN wp_postmeta as pm_product ON pm_product_salon.meta_value = wp_posts.ID
                    INNER JOIN wp_postmeta as pm_product2 ON pm_product_salon2.post_id = pm_product_salon.post_id
                    INNER JOIN wp_postmeta as pm_salon ON pm_salon.post_id = pm_product2.meta_value
    WHERE 1=1
                     AND wp_posts.post_type = 'product' 
    
                     AND pm_product_salon.meta_key = 'sp_product_id'
                     AND pm_product_salon.meta_key = 'sp_salon'
                     AND pm_salon.meta_key = 'salon_region'
                     AND pm_salon.meta_value IN (1511, 1512, 1513)
    
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC LIMIT 0, 24

    Запрос рабочий но выполняется уж слишком долго 0.3289 сек.
    Вся проблема как я полагаю заключается в том что у нас в post_meta информация хранится не в столбцах а в стоках и соответственно приходится постоянно обращаться к postmeta чтобы выбрать единственный элемент для поста.

    Разберу запрос по порядку:
    1. INNER JOIN wp_postmeta as pm_product_salon ON pm_product_salon.meta_value = wp_posts.ID
    (у меня есть post_type товары и есть post_type товары салонов. В товарах салона указывается салон и товар. В салонах указывается регион )
    соответственно в это join я обращаюсь чтобы выбрать все товары которые имеются в товарах салона
    AND pm_product_salon.meta_key = ‘sp_product_id’
    2. Дальше я обращаюсь в туже таблицу чтобы по post_id(товара салона вытащить ID салона)
    INNER JOIN wp_postmeta as pm_product2 ON pm_product_salon2.post_id = pm_product_salon.post_id
    AND pm_product_salon.meta_key = ‘sp_salon’

    Вот здесь я считаю что происходит потеря производительности (во 2)

    3. Ну и в третьем запросе я по полученному ID салона я сравниваю тот ли регион мне нужен
    INNER JOIN wp_postmeta as pm_salon ON pm_salon.post_id = pm_product2.meta_value
    AND pm_salon.meta_key = ‘salon_region’
    AND pm_salon.meta_value IN (1511, 1512, 1513)

    Друзья буду рад любому совету с вашей стороны насчет оптимизации запроса. Представить страшно какое время будет если еще начать проверку на «помещена ли запись в trash»..

Просмотр 9 ответов — с 1 по 9 (всего 9)
  • Я точно не знаю о чем ваш запрос, но удивляет для чего вы делаете JOIN с одной таблицей три раза? И вот это — WHERE 1=1

    Модератор Юрий

    (@yube)

    Друзья буду рад любому совету с вашей стороны насчет оптимизации запроса.

    Не запрос надо оптимизировать, а менять способ хранения данных. В случае активного использования фильтров это вполне оправдано.

    но удивляет для чего вы делаете JOIN с одной таблицей три раза?

    Потому что нет другого способа в одном запросе отфильтровать записи по трем разным метаполям.

    И вот это — WHERE 1=1

    Не знаю, зачем оно тут, но это стандартный способ динамического формирования условий. Статически пишем where 1=1, а затем в цикле «and …».

    WHERE 1=1, не убрал лишнее, просто.

    Потому что нет другого способа в одном запросе отфильтровать записи по трем разным метаполям.

    Спасибо Юрий

    Потому что нет другого способа в одном запросе отфильтровать записи по трем разным метаполям.

    Так вы делаете декартово произведение двух таблиц три раза подряд:

    [[[A × B] x B] x B]

    То есть размер этой таблицы возрастает в 10 раз, потом еще в 10 раз, а потом еще в 10 раз, грубо говоря.

    Почему не записать условия соединения в одну строку?

    …ON a1 = a2 and a3 = a4

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

    Первый ключ получается путем поиска ID товара в postmeta (товара салона — мы это определяем если условие тру pm_product_salon.meta_value = wp_posts.ID где pm_product_salon.meta_key = ‘sp_product_id’), а потом уже узнаем post_id этого товара салона

    таблица имеет вид

    wp_postmeta
    meta_id | post_id | meta_key     | meta_value
    1       | 3       | sp_product_id| 10
    2       | 3       | sp_salon     | 15

    эти метки связаны между собой только через post_id а чтобы нам его узнать мы обращаемся к meta_key = sp_product_id и meta_value = 10

    Вы пишете

    AND pm_product_salon.meta_key = ‘sp_product_id’

    А вот из справки по MySQL http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html

    The LEFT JOIN condition is used to decide how to retrieve rows from table B. (In other words, any condition in the WHERE clause is not used.)

    Я подозреваю, что так же и для INNER JOIN. То есть, вы сначала умножаете, а потом фильтруете, а надо наоборот.

    Еще у вас нет строки

    1. INNER JOIN wp_postmeta as pm_product_salon ON

    В первом запросе.

    У меня есть нехорошие подозрения, что сначала происходит вот это

    INNER JOIN wp_postmeta as pm_product ON pm_product_salon.meta_value = wp_posts.ID
    INNER JOIN wp_postmeta as pm_product2 ON pm_product_salon2.post_id = pm_product_salon.post_id
    INNER JOIN wp_postmeta as pm_salon ON pm_salon.post_id = pm_product2.meta_value

    А потом уже все остальное…

    Ну и это meta_value — оно у вас ключ (индекс)?

    Старался в более понятный вид привести названия и не везде поменял

    Еще у вас нет строки
    1. INNER JOIN wp_postmeta as pm_product_salon ON

    Вот правильный запрос:

    SELECT wp_posts.*
    FROM wp_posts
                    INNER JOIN wp_postmeta as pm_product_salon ON pm_product_salon.meta_value = wp_posts.ID
                    INNER JOIN wp_postmeta as pm_product_salon2 ON pm_product_salon2.post_id = 
    
    pm_product_salon.post_id
                    INNER JOIN wp_postmeta as pm_salon ON pm_salon.post_id = pm_product_salon2.meta_value
    WHERE 1=1
                     AND wp_posts.post_type = 'product' 
    
                     AND pm_product_salon.meta_key = 'sp_product_id'
                     AND pm_product_salon2.meta_key = 'sp_salon'
                     AND pm_salon.meta_key = 'salon_region'
                     AND pm_salon.meta_value IN (1511, 1512, 1513)
    
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC LIMIT 0, 24

    Да meta_value является ключом

    Я подозреваю, что так же и для INNER JOIN. То есть, вы сначала умножаете, а потом фильтруете, а надо наоборот.

    Попробовал как вы говорили, вначале отфильтровать потом перемножать, но из 7 вариантов ни один не был быстрее чем исходный

    EXPLAIN SELECT
        wp_posts.* ,pm_product_salon.*
    FROM
        wp_posts
    INNER JOIN
    (
        SELECT
            j_pm.*
        FROM
            wp_postmeta as j_pm
        WHERE
            j_pm.meta_key = 'sp_product_id'
    )
    as
        pm_product_salon
    ON
        pm_product_salon.meta_value = wp_posts.ID
    
        INNER JOIN
            wp_postmeta as pm_product_salon2
        ON
            pm_product_salon2.post_id = pm_product_salon.post_id
                AND
            pm_product_salon2.meta_key = 'sp_salon'   
    
    INNER JOIN
        wp_postmeta as pm_salon ON pm_salon.post_id = pm_product_salon2.meta_value
    AND
        pm_salon.meta_key = 'salon_region'
    AND
        pm_salon.meta_value IN (15025, 15027, 15026)
    
    WHERE 1=1   AND wp_posts.post_type = 'product'
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC LIMIT 0, 24

    Очень жаль.

    Может быть вам с этим вопросом обратиться на форум по mySQL, где все это обсуждается. Чтобы понять, что там у вас больше всего тормозит, видимо нужно смотреть план запроса и то время, которое тратится на отдельные операции. И запрос можно представить в доступном для понимания виде — в блок-схемах вида сущность связь.

    Рекомендации по ускорению вот например — http://habrahabr.ru/post/41968/ — тут тоже указывают на то, что большое количество JOIN’ов должно тормозить

    Использование большого количества JOIN’ов
    SELECT
    v.video_id
    a.name,
    g.genre
    FROM
    videos AS v
    LEFT JOIN
    link_actors_videos AS la ON la.video_id = v.video_id
    LEFT JOIN
    actors AS a ON a.actor_id = la.actor_id
    LEFT JOIN
    link_genre_video AS lg ON lg.video_id = v.video_id
    LEFT JOIN
    genres AS g ON g.genre_id = lg.genre_id

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

    (у меня есть post_type товары и есть post_type товары салонов. В товарах салона указывается салон и товар. В салонах указывается регион )

    А это так и не понятно — как они у вас хранятся? У вас для одного товара 1 запись в post_type товары? У вас ведь связь простая — Одному салону принадлежит X товаров и если вы не дублируете запись с описанием товара, то у вас должна быть третья таблица со связями и какими-то дополнительными характеристиками (3 таблицы — товары, салоны и товары салонов [id_товара, id_салона, количество]).

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

Просмотр 9 ответов — с 1 по 9 (всего 9)
  • Тема «Оптимизация sql запроса с join» закрыта для новых ответов.