Home arrow MySQL arrow Page 2 - MySQL wizardry

Opening the path - MySQL

"I called in the Wizard on a Friday evening. It was almost six p.m. and I was afraid he had already left for the day. Instead, he answered at the first ring. Wizards never go home early. He recognized me and asked how he could help. I told him. He listed patiently, without asking silly questions in between, and finally said 'I think I could provide you with some useful tool. See you in half an hour' and hung up."

TABLE OF CONTENTS:
  1. MySQL wizardry
  2. Opening the path
  3. Some help from SQL itself
  4. Conclusion
By: Giuseppe Maxia
Rating: starstarstarstarstar / 39
June 05, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
The wizard took a sheet of paper and drew a table


TownMFtotal

"We will go for it manually." He said. "This way, we are going to understand what to ask the database engine to do." It was typical of the Wizard. When he was in this explaining mood, I should better let him talk.
"Let's start with the first row. It's Roma. The employee is male, so we write 1 under M and 0 under F. Then we get the second line. It's again Roma. Which gender is this one? If we have a male, then we are going to add 1 to the value under M, and add a 0 under F, and so on."


TownMFtotal
Roma1+10+0

He looked at me, as if expecting me to see the light and have a magic understanding of the algorithm he was hinting at. My blank stare must have told him that I was still at large. "Don't you get it? It's simple. We sum to M if the employee is male, and we sum to F if she's a female." He stressed the words sum and if. Then he grabbed my keyboard and modified my previous statement:

mysql> SELECT location, SUM(IF(gender='M',1,0)) AS M, SUM(IF(gender='F',1,0)) AS F
FROM locations INNER JOIN employees USING (loc_code) GROUP BY location;
locationMF
Boston12
Cagliari12
London11
Manchester11
Marseille12
Milano20
New York22
Paris11
Roma30
9 rows in set (0.00 sec)

"So we are telling the engine to do exactly the same thing that we would have done manually. Sum ... if. Only the engine will do it faster."

I said "Wow!" but my mind was racing to see how this incredibly simple statement could be of help. "What about the total column?" I asked.
"Oh, that. Here you are." And he modified the statement once more:

mysql> SELECT location, SUM(IF(gender='M',1,0)) AS M,
SUM(IF(gender='F',1,0)) AS F, COUNT(*) AS total
GROUP by location;
locationMFtotal
Boston123
Cagliari123
London112
Manchester112
Marseille123
Milano202
New York224
Paris112
Roma303
9 rows in set (0.00 sec)

"I don't think I really understand, though." I said. "We need to count, but we are summing up. How comes?"
"From the SQL point of view, we are doing the same thing. COUNT of star and SUM of one are the same thing. Try it yourself. Type a 'select COUNT star from employees'".

mysql> SELECT COUNT(*) from employees;
count(*)
24
1 row in set (0.00 sec)

"Now replace COUNT of star with SUM of one."

mysql> SELECT SUM(1) from employees;
sum(1)
24
1 row in set (0.00 sec)

"It's the same!" I said, excited.
"No, actually it's not. COUNT of star is optimized by MySQL, and it is performed from the table descriptor, without actually counting the records. You can't realize the difference in such a small table. If you had one million records, and you were actually counting by groups, you would see that SUM takes a couple of milliseconds more than COUNT, and I think we can live with that. Notice that we could not use COUNT in our cross-tab, because it would have counted all the rows anyway. Try it."

mysql> SELECT location, COUNT(IF(gender='M',1,0)) AS M, 
COUNT(IF(gender='F',1,0)) AS F, 
COUNT(*) AS total 
FROM locations INNER JOIN employees USING (loc_code)
GROUP BY location;
(warning: gives WRONG results!)
locationMFtotal
Boston333
Cagliari333
London222
Manchester222
Marseille333
Milano222
New York444
Paris222
Roma333
9 rows in set (0.00 sec)

"See? That's why we have to sum up, instead of counting. COUNT is a dumb function which will count any piece of junk it finds. SUM has some grace, in its choice."
It looked so trivial that I was ashamed of myself for not having found it alone.
But suddenly I saw something that didn't seem right to me. "Here we have a simple case, where we know all the values that will go into the columns. But what should we do if we don't know? What if we want the departments instead?"
The Wizard took a glance at the diagram and typed:

mysql> SELECT dept from departments;
dept
Development
Personnel
Research
Sales
Training
5 rows in set (0.01 sec)

"Yeah. I see." I said, with a hint of disappointment in my voice. "You mean that I have to compose the query manually, entering a SUM/IF statement for each value in departments?"



 
 
>>> More MySQL Articles          >>> More By Giuseppe Maxia
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: