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?
