MySQL ORDER BY kills my SQL query

I have multiple tables with information about tube videos. I need to join these three tables and grab the required columns and then sort them by most recent ( unix timestamp ). The video column has 1.2 million records, video_data has over 8 million records, and tube has just 22 records.

This query works fine without the开发者_如何转开发 ORDER BY portion:

SELECT vd.video_id, vd.tube_id, v.*, t.tube_title, t.tube_domain FROM video_data vd JOIN video v ON vd.video_id = v.video_id JOIN tube t ON vd.tube_id = t.tube_id LIMIT 100

Above query took 0.0002 sec

However, as soon as I want to sort by timestamp, it takes 4-5 minutes to run, if it isn't reset by the server first.

SELECT vd.video_id, vd.tube_id, v.*, t.tube_title, t.tube_domain FROM video_data vd JOIN video v ON vd.video_id = v.video_id JOIN tube t ON vd.tube_id = t.tube_id ORDER BY v.date_timestamp DESC LIMIT 100

Above query took 272.9157 sec

I added an index to the v.date_timestamp field to see if that would help, but obviously it is not. Maybe putting an index on that column is pointless anyway? Any help would be great, I am still fairly new to SQL...

---

Not positive, but I believe the problem could be that while you have an index on video.date_timestamp, your query has to hit every single video_data entry first to JOIN into video.

You should see big speedup by de-normalizing as follows: duplicate the date_timestamp field to the video_data table and index/sort on that instead of the equivalent one in video.

---

Try running EXPLAIN SELECT … and see what MySQL says: http://dev.mysql.com/doc/refman/5.0/en/explain.html.

Maybe the index, as guess by you, is not used for some reason.

Maybe http://hackmysql.com/case3 helps as well?

---

First of all, I believe that your index is useless if you use DESC - at least I have seen such statements.

Anyway, you could consider doing something like this:

SELECT ... FROM ( SELECT video_id, tube_id FROM video_data ORDER BY timestamp LIMIT 100) as vd INNER JOIN ...

maybe will be faster. the key thing is the limit 100 :)

---

Re-do that join so that the smallest table comes first - this is a good habit.

SELECT vd.video_id, vd.tube_id, v.*, t.tube_title, t.tube_domain 
FROM tube t
INNER JOIN video_data vd
  ON vd.tube_id = t.tube_id
INNER JOIN video v
  ON v.video_id = vd.video_id
 ORDER BY v.date_timestamp DESC LIMIT 100

I bet this will help, as the video data will not be loaded until the 22 tube records are in, so the overall result set will be much smaller. Also, good to show INNER JOIN if that is what you intend.