asked on

Do MysQL Query will less re-read about the same table.

Dear all,

I read this: http://blog.sqlauthority.com/2014/02/25/mysql-profiler-a-simple-and-convenient-tool-for-profiling-sql-queries/

and I am not sure why this one:

SELECT DISTINCT p.maker_id
FROM products AS p
JOIN models m ON p.model_id = m.model_id
WHERE m.model_type = 'PC'
AND NOT EXISTS (
SELECT p2.maker_id
FROM products AS p2
JOIN models m2 ON p2.model_id = m2.model_id
WHERE m2.model_type = 'Laptop'
AND p2.maker_id = p.maker_id
);

Open in new window

can be converted to :

SELECT p.maker_id
FROM products AS p
JOIN models AS m ON p.model_id = m.model_id
WHERE m.model_type IN ('PC', 'Laptop')
GROUP BY p.maker_id
HAVING COUNT(CASE WHEN m.model_type = 'PC' THEN 1 END) > 0
AND COUNT(CASE WHEN m.model_type = 'Laptop' THEN 1 END) = 0;

Open in new window

with the same result?

what is the

HAVING COUNT(CASE WHEN m.model_type = 'PC' THEN 1 END) > 0
AND COUNT(CASE WHEN m.model_type = 'Laptop' THEN 1 END) = 0;

Open in new window

is about?

Pratima

SELECT DISTINCT p.maker_id
FROM products AS p
JOIN models m ON p.model_id = m.model_id
WHERE m.model_type = 'PC'
AND NOT EXISTS (
SELECT p2.maker_id
FROM products AS p2
JOIN models m2 ON p2.model_id = m2.model_id
WHERE m2.model_type = 'Laptop'
AND p2.maker_id = p.maker_id
);

Meaning of this is - required distinct maker_ id where model type is only PC not Laptop

in new query

GROUP BY p.maker_id
HAVING COUNT(CASE WHEN m.model_type = 'PC' THEN 1 END) > 0
AND COUNT(CASE WHEN m.model_type = 'Laptop' THEN 1 END) = 0;

GROUP BY - use for distinct data

HAVING COUNT(CASE WHEN m.model_type = 'PC' THEN 1 END) > 0 - use for make sure model type is PC

AND COUNT(CASE WHEN m.model_type = 'Laptop' THEN 1 END) = 0; - use to make sure that it will not contain laptop as model type

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

basically is using the HAVING COUNT or count in this way can minimize the re read problem but is it just because of the having or becauase of the simple calculate in the where cause instead of "NOT EXISTS " ?

this will make the MySQL faster ?

Which one is the key part ?

Julian Hansen

It is a combination - grouping of records on like makerid and then eliminating the records you don't want based on the count values.

marrowyung

ASKER

but grouping of records on like makerid don't speed up thing, right ?

so eliminating the records you don't want based on the count values is the key part ?

SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

marrowyung

ASKER

then the inner join (to replace the sub query) is not that efficent enought than that?

Usually the key thing should be that any thing in the where cause should be index to avoid table scan, this should provide much performance gain than this kind of group by, right?