Two Table Query Slow: File Sort Problem

Question

I'm trying to query the latest entries on two different tables as one query:

SELECT
    news.title AS news_title,
    news.sid AS news_id,
    downloads.lid AS download_id,
    downloads.title AS download_title
FROM
    news,
    downloads
ORDER BY
    news_id DESC,
    download_id DESC
    LIMIT 0,10

The query is really slow and it says "Using temporary; Using filesort" when I do an EXPLAIN. Each table has about 2,000 items. Each table's primary key Index is the id (lid and sid).

Answer 1

You have no join condition so it's joining every row in news with every row in downloads. This is called a cross join or cartesian product. So if news has 1000 rows and downloads has 3000 rows you will get 3 million rows returned.

You probably want something like this:

SELECT news.title AS news_title,
       news.sid AS news_id,
       downloads.lid AS download_id,
       downloads.title AS download_title
FROM news n
JOIN downloads d ON n.some_column = d.join_column /* fill this part in */
ORDER BY news_id DESC,download_id DESC LIMIT 0,10

In all likelihood one join column will be a primary key of one column and a foreign key in the other.

Oh, make sure the foreign key is indexed too.

Answer 2

You'll want to join the tables. What you have done is created a query that spits out a Cartesian product - every row from one table and every row from the other. That's why it's slow.

You want to add a WHERE table A.key = table b.field to join them.

Lance