Today I have to thank Paulinho about everything I learned in my Post Graduate APGS (Analisys, Project and Management of Information System - Latu Sensu ). Paulinho is a database specialist and teaches DB subject at PUC-RJ (Pontífica Universidade Católica) in Rio de Janeiro.
I remember I didn 't give it a shit about that subject, used to joke about that and I didn 't believe in that f#&*$ng "explain" command at that time.
I have been involved in a project which uses MySql, JBoss Seam, JBoss Server, JAX-WS and RedHat Linux... Pretty cool stuff!!!
I had to run one query quite big with a couple of joins. I left running for 1 hour and didn 't finish.
This time I remembered Database classes with the teacher Paulinho and decided to "explain" that query.
You don't need to be a DBA to do it, just need to put the word "explain" in front of your query and run it!!! like this:
explain select * from employee inner join department on ...
The most important thing in your result is the column "Type" which tells you how bad is you query.
For the worse to the best ones, here are possibles values of "Type" (in Mysql):
All, index, range, Eq_ref, const, system
If you see "All" in any line of the "type" column means you have your ass in the line (tá fudido).
If you carefully analyze the result you can much improve your query. In my case I just created a few indexes and removed some calculated fields from the clause where.
The original query was running in one hour - The new query ran in less than one minute making inner join in tables with over 1 million lines each!!!
Now we are going to take a look at Ingres database... Let have a go and see it... write about that later...
Leonardo Correa
Java Analyst/Developer, soccer player and beer drinker
Leonardo,
ReplyDeleteYou choosed a very good name for this post.
Success for you.