mysql query speedup

[radd]

I have three tables with these fields that have about 5k rows each

 

USERS

id | email

 

FLASH2TIMBRES

id | id_user | photo_name | valid

 

VOTES

id | id_user | id_photo

 

 

 

I use a query that makes my site run very slow and I think there's room for improvising this:

 

SELECT
  `users`.`email`,
  `flash2timbres`.`id`,
  `flash2timbres`.`id_user`,
  `flash2timbres`.`photo_name`,
  `flash2timbres`.`time`,
COUNT(`votes`.`id` ) AS nbvotes
FROM `users`, `flash2timbres`
LEFT JOIN `votes` ON
  `flash2timbres`.`id` = `votes`.`id_photo`
WHERE
  `valid` = 1 AND `flash2timbres`.`id_user` = `users`.`id`
GROUP BY
  `flash2timbres`.`id`
ORDER BY DESC LIMIT 0, 30

 

I use Codeigniter and I don't know if Active Records might be faster than a query String. Thanks

[radd]

I don't know how to edit this post but I had an error on my SELECT

 

...

ORDER BY `flash2timbres`.`id` DESC LIMIT 0, 30

Edited February 19, 2013 by radd

[radd]

Solved this by using INDEXES.

[Barand]

Also, INNER JOIN is faster than LEFT JOIN