Mysql - 查询和索引

Question

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!

Answer 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 ?