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

Reply via email to