Slow SELECT, too many rows examined.

[Jehu]

# Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 32023
SELECT id FROM entries WHERE active='1' AND preview='0' ORDER BY posted DESC LIMIT 10,1;

If the query is limited to 10 results, why does it examine all rows? Is there any way to make the query stop once it's got enough rows? (This is literally killing my website.)

posted is a date field, every field that's used with = is indexed

What can I do?

[recchi_8]

think about what your query is doing...
it has to go through your entire table finding where active = 1, then even if it hits 10 it has to process the rest of the table because now you want to ORDER BY a field. So that means it has to gather up ALL the data in the table, then take the first 10 records in descending order.

[Jehu]

Now I understand. But how on Earth do I get number of the newest rows without processing the whole table? There's got to be some way!

[1337-Dev]

$count=1
while($row=mysql_fetch_assoc($result)) {
$count++
if($count == 10) {
// stop it 
} 

[recchi_8]

actually 1337 that would be a really inefficient way of doing it. What you're doing is getting ALL the matching database rows then in php just displaying the first 10.

If there were a million rows in the DB you'd be wasting a ton of bandwidth. The correct way would be to do an order by on a date field or some field you want to limit by, then do either a TOP(sql server) for a LIMIT(mysql).

This way the database isn't returning wasted rows.

[wwb_99]

Add an index on Posted, should speed up rowscan.

WWB

[cumquott]

Hmmm, it seems that even if the right indexen are used, MySQL (that's what I'm using) still won't use the correct indexes. For example, these two queries give the same result (both fields are indexed and I ran OPTIMIZE TABLE):

select max(MI01_PART_NO) from PRTMST
where MI01_WHSE = 1 and MI01_PART_NO < 'COM-056'

-or-

select MI01_PART_NO from PRTMST
where MI01_WHSE = 1 and MI01_PART_NO < 'COM-056'
order by MI01_PART_NO desc limit 1

The second one makes the server HDD grind pretty good. EXPLAINs on each of these give, respectively:

Row Field Value
1 table PRTMST
1 type ALL
1 possible_keys MI01_PART_NO,MI01_WHSE
1 key
1 key_len
1 ref
1 rows 50763
1 Extra where used

Row Field Value
1 table PRTMST
1 type range
1 possible_keys MI01_PART_NO,MI01_WHSE
1 key MI01_PART_NO
1 key_len 27
1 ref
1 rows 50763
1 Extra where used; Using filesort

I want a not too dissimilar result: quickly get the previous record. Using the same technique (limit 1) to get the next record is quite fast. This is "moon surface erosion slow".

Ideas?

[Jehu]

recchi_8: Bear with me, but "posted" is a date field, so isn't my query already doing what you say to do? But it scans the entire table...