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
