Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-02-26
7
Medium Priority
?
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 59

Accepted Solution

by:
Julian Hansen earned 1000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 59

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 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

609 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