Hi all, I have what is probably a quite standard question and would love to know how you would all approach this scenario:
I have a table in a database that has approx. 190,000 records in it. The table is currently 128MB in size and I'm happy that it is well constructed with no data duplication and sensible indexes. I'm using MySQL 3.28.58. and my question is about querying this volume of data efficiently. The table holds forum threads (several years worth) so a common query running on the table is to bring back the top 50 or 100 threads from a board within the forum. To do this I'm using a LIMIT on my query and for the paging through the data (i.e. the first 100 threads, the next 100, etc) I use the LIMIT n,x syntax. Threads are sorted by date (most recent to the top). This is fine and it works well but I'm concerned it's not the most efficient way to do this because the use of LIMIT is causing the whole table to be scanned each time. Here is a typical (simplified) query: SELECT * FROM thread WHERE thread.status='L' AND thread.boardid=1 ORDER BY created DESC LIMIT 100,50 This takes over 1.02 seconds to process. Running an EXPLAIN on my query shows that it's using one key (boardid), but in the Extra field it shows it is having to use a filesort on the data. 5701 rows were used in order to bring back the final 50 - that's every single thread for this board. What I'm trying to figure out is a more efficient way of selecting a block of 50 or 100 records from any point in my table without MySQL needing to sort/check them all first. One thought I did have was that the Primary Key on my table is called threadid - and I thought that instead of bring back the data in my original query, I could collect nothing but the thread IDs and then use a separate query that does something like: "SELECT * FROM thread WHERE threadid IN (...)" (where ... = all of the IDs previously selected). Would the fact that threadid is my primary key make the original LIMIT/sort faster? Any thoughts appreciated. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
