Solved

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

Posted on 2014-02-26
7
217 Views
Last Modified: 2014-03-06
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?
0
Comment
Question by:marrowyung
  • 3
  • 2
  • 2
7 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39888224
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
0
 
LVL 52

Accepted Solution

by:
Julian Hansen earned 250 total points
ID: 39888311
The first query basically says find all records where the products contains 'PC' and where there isn't another record for the same manufacturer that makes a laptop.

In otherwords only find manufacutures of PC's

The second query obviously does the same thing but uses a slightly different technique.

This query groups all records that have the same manufacturer id together where the manufacture supplies either PC's or laptops and then sums (COUNT) the number of each device a particular manufacture makes.

So what could come out of the query - before the having is applied is

+----------+----------------------------+--------------------------------+
| MakerId  | Count(m.modelType  = 'PC') |  COUNT(m.modelType = 'Laptop') |
+----------+----------------------------+--------------------------------+
|  1       |           1                |            1                   |
|  2       |           1                |            0                   |
|  3       |           0                |            1                   |
+----------+----------------------------+--------------------------------+

Open in new window

The HAVING clause then exclude the records where PC is  0 or LAPTOP is > 0

Or to put it another way

Includes where PC is > 0
HAVING COUNT(CASE WHEN m.model_type = 'PC' THEN 1 END) > 0

Open in new window

AND
where Laptop = 0
COUNT(CASE WHEN m.model_type = 'Laptop' THEN 1 END) = 0

Open in new window

Leaving you with
+----------+----------------------------+--------------------------------+
| MakerId  | Count(m.modelType  = 'PC') |  COUNT(m.modelType = 'Laptop') |
+----------+----------------------------+--------------------------------+
|  2       |           1                |            0                   |
+----------+----------------------------+--------------------------------+

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39891228
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 ?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 52

Expert Comment

by:Julian Hansen
ID: 39891447
It is a combination - grouping of records on like makerid and then eliminating the records you don't want based on the count values.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39893812
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 ?
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 250 total points
ID: 39893911
grouping of records on like makerid will speed up thing .
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39893923
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?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now