| Is there a better way to page data than using LIMIT ? [message #1542] |
Tue, 17 July 2007 14:48  |
 richd
Messages: 3
Registered: July 2007 |
Junior Member |
|
|
Hi,
The subject says it all really. I am curious if, on relatively large tables, there is a better way to get 'paged' data than using the LIMIT function.
For example I have a comments table, it contains around 300k comments. The table structure is:
comment_id INT
item_ref INT
author_ref INT
date_created DATE_TIME
status ENUM('pending','live','deleted')
The actual comment text is held in comment_body:
comment_ref INT
message TINYTEXT
And finally I've a comment_stats table:
comment_ref INT
total_views INT
I split the 'total views' out into a separate table because this table receives a large number of update requests, where-as the main comment and comment_body tables are heavily read, but rarely modified.
Keeping the comment text itself out of the main comment table kept the table fixed. comment_body is LEFT JOINed on the comment_id to comment_ref.
comment_id is my primary key (auto inc, unsigned)
In order to page through the comments, which are displayed based on date_created, I'm using LIMIT X,Y in order to show 50 comments per page. The 'item_ref' field is the link to the item being commented on, so for example some items may only have a couple of comments, while others will have thousands.
I program with PHP 5 (PDO extension) and I am having to run my query once with a COUNT(*) at the start in order to get back table totals, and then again with LIMIT at the end to get the block of rows that I want to display. Using explain shows that it is checking ALL comments matching item_ref, even though it only returns the 50 I require.
Is there a better way to do this? I'm perfectly happy to move my query into a stored procedure if it will help (and use a cursor to loop through the data?) but is the problem more intrinsic than that? Perhaps the solution isn't MySQL based at all, maybe I should just be looking at caching say the first few pages worth of results for each item and not worry about the MySQL hit?
Eitherway I'd truly appreciate your advice.
|
|
|
|
|
| Re: Is there a better way to page data than using LIMIT ? [message #1546 is a reply to message #1542 ] |
Sun, 22 July 2007 12:54  |
carpii
Messages: 46
Registered: November 2006 |
Member |
|
|
consider denormalising a little bit
You do an expensive count, just to see how many pages there are.
The paging isnt a performance problem, but the count potentially is.
But if instead, you were to store num_comments in the items table, you could just figure out how many pages there are based on that, and jump straight to the query with a LIMIT
|
|
|
Members
Search
Help
Register
Login
Home
