Sol Sol - 5 months ago 20
MySQL Question

Count distinct with left join query executing long time

I have about 140k raws in all these tables:

SELECT COUNT( DISTINCT p.product_id ) AS total
FROM bh_product p
LEFT JOIN bh_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN bh_product_to_store p2s ON ( p.product_id = p2s.product_id )


Is it normal that execution of this query takes about 3 seconds?

All tables have indexes on product_id field.

Can it be somehow improved?

UPDATED:
Original query:

SELECT COUNT( DISTINCT p.product_id ) AS total
FROM bh_product p
LEFT JOIN bh_product_description pd ON ( p.product_id = pd.product_id )
LEFT JOIN bh_product_to_store p2s ON ( p.product_id = p2s.product_id )
WHERE pd.language_id = '2'
AND p.status = '1'
AND p.date_available <= NOW( )
AND p2s.store_id = '0'
AND (
pd.name LIKE '%душевые%'
OR pd.tag LIKE '%душевые%'
OR LCASE( p.model ) = 'душевые'
OR LCASE( p.sku ) = 'душевые'
OR LCASE( p.upc ) = 'душевые'
OR LCASE( p.ean ) = 'душевые'
OR LCASE( p.jan ) = 'душевые'
OR LCASE( p.isbn ) = 'душевые'
OR LCASE( p.mpn ) = 'душевые'
)


UPDATED: It figured out that the server were running other intensive process, that was slowing sql processing.
After turning off other process perfomance become acceptable.

Answer

You can start from this query:

SELECT COUNT( 
  DISTINCT p.product_id ) AS total
FROM bh_product p
INNER JOIN bh_product_description pd 
ON p.product_id = pd.product_id
  AND pd.language_id = 2
INNER JOIN bh_product_to_store p2s 
ON p.product_id = p2s.product_id 
  AND p2s.store_id = 0
WHERE p.status =  '1'
AND p.date_available <= NOW( ) 
AND (
pd.name LIKE  '%душевые%'
OR pd.tag LIKE  '%душевые%'
OR 'душевые' IN ( p.model , p.sku , p.upc , p.ean , p.jan , p.isbn , p.mpn ))

But your most weak point here is your WHERE clause. you are trying to search everywhere. That is not very smart. I am pretty sure that p.ean is barcode and can't be equal to душевые same for p.isbn. So you should change your query to do only what you really need to do. But not filter everything with hope to "catch the fish".

Update Check this query (should be much faster then your 1st one):

SELECT COUNT( 
  DISTINCT p.product_id ) AS total
FROM bh_product p
INNER JOIN bh_product_description pd 
ON p.product_id = pd.product_id
  AND pd.language_id = 2
INNER JOIN bh_product_to_store p2s 
ON p.product_id = p2s.product_id 
  AND p2s.store_id = 0