1

I want to get ideas and thoughts about what/how to use indexes and better SQL query.

Here is an example of my SQL query:

SELECT albums.id AS album_id, albums.name AS album_name, albums.upc, albums.status, 
albumstatus.description AS album_status, DATE_FORMAT(albums.created, '%Y-%m-%d') AS created_date,  
albuminfos.label   
FROM albums,albumstatus, albumtypes, albuminfos  
WHERE albums.status = albumstatus.id AND albumtypes.id = albums.albumtype_id AND albums.id  = albuminfos.id  
AND albums.account_id = 9999  
AND albums.status IN (1, 2)

And I tried using mysql EXPLAIN the output:

id  select_type  table        type    possible_keys                           key         key_len  ref                                              rows  Extra        

 1  SIMPLE       albums       ref     PRIMARY,account_id,status,albumtype_id  account_id  4        const                                            4148  Using where  
 1  SIMPLE       albumstatus  eq_ref  PRIMARY,id                              PRIMARY     4        albums.status             1  Using where  
 1  SIMPLE       albumtypes   eq_ref  PRIMARY                                 PRIMARY     1        albums.albumtype_id       1  Using index  
 1  SIMPLE       albuminfos   eq_ref  PRIMARY                                 PRIMARY     4        albums.id                 1

Im the guy that uses a lot of LEFT JOIN and connecting them on Primary Key... My friend told me to use indexes to improve increase the speed getting the result.. I got confused when I found that the indexes slow down the speed of writing queries : INSERT,DELETE, UPDATE where album and albuminfos tables may have a new/updated record in anytime.. so I am so lost and so I want to listen and get the ideas from the professional:

  1. is my query good?
  2. What do I need to know in mysql EXPLAIN?
    • Is indexes possible to use? If yes..How?
  3. The right and wrong in my setup?

Thanks!

Drixson Oseña
  • 3,631
  • 3
  • 23
  • 36
  • all your tables are using indexes. PRIMARY means primary key which is index also. – DevZer0 Jul 01 '13 at 07:45
  • You are using *a lot* of `LEFT JOINs`? Where are they? – ypercubeᵀᴹ Jul 01 '13 at 08:06
  • @ypercube I always thought `FROM albums,albumstatus, albumtypes,albuminfos WHERE albums.status = albumstatus.id AND albumtypes.id = albums.albumtype_id AND albums.id = albuminfos.id ` are always in left join, correct? – Drixson Oseña Jul 01 '13 at 08:18
  • These are not outer joins, neither left not right. They are simple inner joins, using the old (SQL-89) implicit join syntax. – ypercubeᵀᴹ Jul 01 '13 at 08:21
  • @ypercube thank you, I understand now. I have a backup sql statement for this where it uses LEFT JOIN. Like kmas pointed out. – Drixson Oseña Jul 01 '13 at 08:33

2 Answers2

1

Question 1 :

Separate joins from clause where (it is easier to read and more logical), otherwise your query seems right in the older way.

SELECT albums.id AS album_id, albums.name AS album_name, albums.upc, albums.status, 
    albumstatus.description AS album_status, DATE_FORMAT(albums.created, '%Y-%m-%d') AS created_date,  
    albuminfos.label   
FROM albums
LEFT OUTER JOIN albumstatus sta ON  albums.status = albumstatus.id
LEFT OUTER JOIN albumtypes  typ ON  albumtypes.id = albums.albumtype_id
LEFT OUTER JOIN albuminfos  inf ON  albums.id     = albuminfos.id  
WHERE  albums.account_id = 9999  AND albums.status IN (1, 2) ;

Your indexes are right.

For the compound index (@ypercube):

    ALTER TABLE albums ADD INDEX idx_account_id (account_id ASC, status ASC) ;

Question 2 :

Force MySQL to use two indexes on a Join

But MySQL does it usually well.

Question 3 :

How many records do you have in your table albums ?

Community
  • 1
  • 1
kmas
  • 6,401
  • 13
  • 40
  • 62
  • 1
    Reading the query again, a compound index either on `(account_id,status)` or on `(status,account_id)` would be more efficient than the 2 separate indexes (only the `account_id` index is used anyway). – ypercubeᵀᴹ Jul 01 '13 at 08:15
  • @kmas albums table if not filtered is 65k+ records – Drixson Oseña Jul 01 '13 at 08:16
  • It is not a big one, so you shouldn't have performance problem with it. A compound index can improve your query as ypercube told you, but not necessarily. – kmas Jul 01 '13 at 08:20
  • @kmas yes I understand but the problem is that i am pulling this data from a database across the country.. soon the number of records will increase. Does it mean there's nothing to improve? I got everything work fine? – Drixson Oseña Jul 01 '13 at 08:24
  • Your query seems right to me. Just create the compound index if your query is slow. Then, if you have performance problems, you have to check your my.cnf/my.ini file to set a cache, add more memory to MySQL, etc. But it is another problem. – kmas Jul 01 '13 at 08:42
  • Thanks! This may sum it up. – Drixson Oseña Jul 01 '13 at 08:49
0

Can you provide more details on the tables by using "SHOW CREATE TABLE "?

From the EXLPAIN output it seems that your current keys (which are the PRIMARY ones) are sufficient for this SQL. As you can see, all the lines out of the EXPLAIN state that a KEY is being used (the PRIMARY one).

I suggest reading http://dev.mysql.com/doc/refman/5.0/en/explain-output.html on how to interpret the output, most important things to look out for are, possible_key being NULL (meaning that you are not using any key for the data selection), rows being a small number (unless you are not matching anything, then the total number of rows in the table is ok), Extra shouldn't say things like Using temporary; Using filesort which means a slower join/sort/select/search is being preformed.

Noam Rathaus
  • 5,405
  • 2
  • 28
  • 37
  • Thank you for sharing the idea. So a good setup is always to put a primary key for every tables if possible? if not what cases that can be? – Drixson Oseña Jul 01 '13 at 08:29
  • http://stackoverflow.com/questions/2878272/when-i-should-use-primary-key-or-index – kmas Jul 01 '13 at 08:57