[TomazWS]

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?

[Catalyst]

You'll want to make sure your using covering index to improve performance, see:

http://peter-zaitsev...l.com/6949.html

and for more on optimizing that kind of query:

http://www.mysqlperformanceblog.com/2006/0...e-optimization/

I think the ORDER BY LIMIT approach is probably the best, but you just have to set it up correctly to perform well.