Hi, I have a 6000-row table for "products". Let's say I need to select the latest 30 rows to display on the website, the simple query would look like this right?

SELECT * FROM  products ORDER BY id DESC LIMIT 30

In phpMyAdmin, it shows that query took 0.0039 sec. Though, if I do an "EXPLAIN", it shows that it is scanning through all 6000 rows in the table:

id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra
1  | SIMPLE      | products | index | NULL          | PRIMARY | 4       | NULL | 6908 |

For the sake of server memory concern (I'm getting numerous warnings from hosting company regarding memory issues), I've try to find a better way to do queries, and I'm not sure if I am heading the right direction or not, I came across creating the follow:

SELECT * FROM products
WHERE id > ((SELECT MAX(id) AS FROM products) - 30)
ORDER BY id DESC

It does the exact SAME thing as the short query above, except the query is reference off an indexed column. Though, in phpMyAdmin, it took 0.0047 sec to complete. Following is the "EXPLAIN":

id | select_type | table    | type  | possible_keys | key     | key_len | ref  | rows | Extra
1  | PRIMARY     | products | range | PRIMARY       | PRIMARY | 4       | NULL | 75   | Using where
2  | SUBQUERY    | NULL     | NULL  | NULL          | NULL    | NULL    | NULL | NULL | Select tables optimized away

As you can see, the query only scanned 75 rows instead of 6000 rows... but why is it taking longer to complete, is it because of the nested query?

Or the most important question, am I even heading the right direction?

First thing would be to get rid of the "SELECT *" and use fieldlists

http://www.webmaster-talk.com/the-da...tml#post766485

secondly I'd be looking to upgrade the hosts to a better one

6,000 rows is nothing. As you can see from the execution time.

Traversing an index has a cost associated with it. SQL will prefer a table scan for a very small table, which you might be seeing, although the break even point should be much smaller. As Chris says, select * isn't helping, but seems the same in both queries.

Probably, depending on how MySQL was written, the fact that you use ID in a predicate (where clause vs order by + limit) must trick the optimizer.

Everybody has valid points. However, no one mentioned that your ORDER BY clause is to blame for scanning all 6000 table rows. Without a predicate that is supported by an index within the where clause, all rows must first be scanned, then sorted. One trick which I've used when just a few of the columns are needed is to create a composite index which contains all the columns needed to satisfy the query requirements. This technique allows the query to be completed using only the index without having to "join" to the physical table to extract the addition columns not in the query. There are also some Oracle-specific techniques, but that's apples & oranges...good luck!