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
Solved

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

Posted on 2014-02-26
7
218 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 55

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 55

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

839 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