Re: Optimising LIMITs

Hi Richard! Step 1 --------------- Is it right? 1. threadid value is unique. 2. threadid value is auto_increment. 3. if threadid(2) > threadid(1) => created(2) > created(1). You can do the following: 1. Create the index on two fields: (boardid, status) 2. ALTER TABLE board ORDER BY threadid DESC; Here is new query to fetch a block of records: SELECT * FROM board WHERE boardid = 1 AND status = "L" LIMIT 100,50 Step 2 ---------------- The average size of row in your table is 128,000,000 / 190,000 ~= 675 bytes. Split your table in two tables, like: CREATE TABLE boardinfo ( threadid INT(4) UNSIGNED AUTO_INCREMENT, PRIMARY KEY( threadid ), boardid INT(4) UNSIGNED, status ENUM( 'L', '....' ), created TIMESTAMP ... (all fixed fields ) ) ALTER TABLE boardinfo CREATE INDEX main (boardid,status`) ALTER TABLE boardinfo ORDER BY threadid DESC CREATE TABLE boarddata ( threadid_ref INT(4) UNSIGNED, PRIMARY KEY ( threadid_ref ), threaddata LONGTEXT ) Here is your query after: SELECT * FROM boardinfo LEFT JOIN boarddata ON threadid = threadid_ref WHERE boardid = 1 AND status = "L" LIMIT 100,50 Sorry for my englist. Best regards, Pavel > 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