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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
