Indexing a mysql field.

[chewie07]

Hello, i need a little help with this query.

SELECT
n_news.id,
n_news.title,
n_news.date,
n_news.cat_id,
n_news.count,
n_cats.id as cat_id,
n_cats.nombre as cat_name,
n_cats.shortn as cat_shortn,
COUNT(DISTINCT n_comments.id) as com_num
FROM
n_news
LEFT JOIN
n_comments ON n_news.id = n_comments.news_id
LEFT JOIN
n_cats ON n_news.cat_id = n_cats.id
WHERE
n_news.date > 1126255152
GROUP BY
n_news.id
ORDER BY
n_news.date
DESC
LIMIT 20

When explaining it:

http://fusionnintendo.net/files/chewie/explainsql.jpg

So, as you see i am using indexes, but in the first table (the one i call with the where clause) it is still searching in all the rows in the table (2007). what i want is to reduce that number only to the number of rows needed (exactly as i did with the left join statements).

Any idea?

Regards.

[Nedals]

.... WHERE n_news.date > 1126255152

This requires a seach of the entire n.news table (2007 records) regardless of the LIMIT clause

If the date column were indexed, it will still need to search all records where date > 1126255152 and,
if ALL dates were greater than this, it would again need to search the entire table.

[chewie07]

Thx for your answer :)

So, then is there a way to select only the last 20 inserts of the table without mysql having to search in all the table?

[Nedals]

This is only a guess but it might work...

Remove the SELECT COUNT(....), the ORDER BY, and the GROUP BY.
Definately the ORDER BY; the others you may be able to leave.

That should return the last 20 entries (LIMIT 20) without a search. (but it could be the first 20 entries depending on how the mySQL API collects data)

You can then compute the COUNT and SORT after the fact.