SQL sorgu çok uzun sürüyor

3 Cevap php

I am trying to optimize the SQL query listed below.
It is basically a search engine code that retrieves products based on the products name. It also checks products model number and whether or not it is enabled.
This executes in about 1.6 seconds when I run it directly through the phpMyAdmin tool but takes about 3 seconds in total to load in conjunction with the PHP file it is placed in.

Ben bir kategori arama işlevselliği eklemek gerekiyor ve şimdi YARDIM, MySQL sunucu çöküyor!

Justin

SELECT DISTINCT
    p.products_id,
    p.products_image,
    p.products_price,
    s.specials_new_products_price,
    p.products_weight,
    p.products_unit_quantity,
    pd.products_name,
    pd.products_img_alt,
    pd.products_affiliate_url
FROM
    products AS p
    LEFT JOIN
        vendors v
    ON
        v.vendors_id = p.vendors_id
    LEFT JOIN
        specials AS s
    ON
        s.products_id = p.products_id
        AND
        s.status = 1,
    categories AS c,
    products_description AS pd,
    products_to_categories AS p2c
WHERE
    (
        (
            pd.products_name LIKE '%cleaning%'
            AND
            pd.products_name LIKE '%supplies%'
        )
        OR
        (
            p.products_model LIKE '%cleaning%'
            AND
            p.products_model LIKE '%supplies%'
        )
        OR
        p.products_id = 'cleaning supplies'
        OR
        v.vendors_prefix = 'cleaning supplies'
        OR
            CONCAT (CAST (v.vendors_prefix AS CHAR), '-', CAST (p.products_id AS CHAR)) = 'cleaning supplies'
    )
    AND
    p.products_status = '1'
    AND
    c.categories_status = '1'
    AND
    p.products_id = pd.products_id
    AND
    p2c.products_id = pd.products_id
    AND
    p2c.categories_id = c.categories_id
ORDER BY
    pd.products_name

3 Cevap

left join specials as s on s.products_id = p.products_id and s.status = 1,

Orada tam bir tablo taraması ettik. Bu tür verilere (tinyint, doğru / yanlış) iyi InnoDb tarafından işlenmez. Oracle Bir bitmap indeksi gibi bir şey var, bu da düşük seçicilik indeksleri için iyidir. Sen, içinde bulunduğu, böyle bir şey, bir alt sorgu kullanabilirsiniz:

...
where 
   p.product_id IN (SELECT S.status FROM specials s WHERE s.status = 1)
AND
   (
          ( pd.products_name like '%cleaning%' and pd.products_name like '%supplies%' ) or 
...

Sonra, bu alt sorgu sonuçlarını önbelleğe Sorgu önbelleği için dua ediyorum.

SİZİN İZAH POST!

select p.products_id, p.products_image, p.products_price, 
    p.products_weight, p.products_unit_quantity, 
    s.specials_new_products_price, 
    pd.products_name, pd.products_img_alt, pd.products_affiliate_url 
from products as p 
    left join vendors v ON v.vendors_id = p.vendors_id
    left join specials as s on s.products_id = p.products_id, 
    left join products_description as pd on pd.products_id = p.products_id
    left join products_to_categories as p2c on p2c.products_id = p.products_id 
    left join categories as c on c.categories_id = p2c.categories_id
where 
       (
          ( pd.products_name like '%cleaning%' and pd.products_name like '%supplies%' ) or 
          ( p.products_model like '%cleaning%' and p.products_model like '%supplies%' ) or 
          p.products_id = 'cleaning supplies' or 
          v.vendors_prefix = 'cleaning supplies' or 
          CONCAT( CAST(v.vendors_prefix as char), '-', CAST(p.products_id as char) ) = 'cleaning supplies'
       ) 
    and p.products_status = '1' 
    and s.status = 1
    and c.categories_status = '1'
group by p.products_id
order by pd.products_name

Emin olun şu endeksleri belirledik:

create index prod_prodid_status on products(products_id, products_status);
create index vendors_vendorid on vendors(vendors_id);
create index specials_prodid_status on vendors(products_id, `status`);
create index prod_desc_prodid_prodname on products_description(products_id, products_name);
create index prod_cat_prodid_catid on products_to_categories(products_id, categories_id);
create index categories_catid_status on categories(categories_id, categories_status);

And i hope all the fields with "id" in their name are integers

Bu deneyin, sorgunuzu bölmek ve kullanmak sendika işe umut

etiketler