Is there any other way to optimize this query SELECT name FROM `users` ORDER BY id DESC LIMIT 0 , 100 PHP: I want to display the last 100 users. Now im wondering if there is a way to optimize it because in explain it seek all rows in users table and too slow in large table thanks. By the way 'id' is primary index
Assuming the ID is a serial or sequence Select max(id) from users SELECT name from users where id >maxid-100 order by id. But this will not work for most ACID compliant databases since inserts can be rolled back, for Mysql with MyIsam this might work. An alternative is to use a hint column to decrease the amount of work Mysql needs to perform. 1)create a column last100users_hint for each new user this needs to be true. create an index on this column. 2) Update the last100users_hint column every 24 hours by marking only the last 100 ids true using an update version of the slow query in your first post. 3) When user needs the last 100 list, then perform the query using your first query plus an AND last100users_hint=true. The query planner should use you hint column to discard most records, the remaining records will then be used in your first query. So the hints column will reduce your millions of users => last 100+users created in the last 24 hours. Your original query will reduce the last 100+users created in the last 24 hours=>last 100 users.
The query planner should use you hint column to discard most records, the remaining records will then be used in your first query.
You say it is slow. Maybe it's the type of table and MySQL configuration you are using that is causing the problem. You might wish to look into INNODB MySQL table types and configure MySQL (ini) to use them for your needs.
Hehe, can't make it more optimized then what it is right now, sorry. Maybe take a lock at the hardware and make sure that the server is not overloaded.