I have a script that reads data records from MySQL and creates a table of all the records and their subsequent data for viewing. Each page of the script will only show 10 records, but with over 10,000 records, there are many many pages of records... thus I have pagination links at the bottom.
Now for the pagination to work correctly, I have to count the total number of records in the specified query, but to display only the 10 records per page, I have to run the same query a second time and add the LIMIT clause with start and display limit parameters. So right now I am calling the same query essentially twice, only the second time I specify the LIMIT clause. These queries are rather huge with multiple joins and many many columns of data.
Is there any way I can optimize this process and get the total record count of the specified query for pagination purposes, but also be able to limit the start and display count for each page as aforementioned. Or is this something I just have to use 2 seperate queries for? Thanks for any advice and help. Take care.
Can I optimize this process?
Moderator: General Moderators
- seodevhead
- Forum Regular
- Posts: 705
- Joined: Sat Oct 08, 2005 8:18 pm
- Location: Windermere, FL
Fot counting the rows use something like this:
Replace field_name with a real field name. Try to choose a field which is an integer - an id field is a good idea.
This query should take less than 0.5 seconds with a table with over 10 million rows (read this for more information).
Now for the second query... All you need to know is where to start.
Ok, assuming you have a variable $max which holds the max number of records to show in each page and a variable called $page which holds the current page number. All you've got to do is this:
Code: Select all
SELECT COUNT('field_name') FROM table_nameThis query should take less than 0.5 seconds with a table with over 10 million rows (read this for more information).
Now for the second query... All you need to know is where to start.
Ok, assuming you have a variable $max which holds the max number of records to show in each page and a variable called $page which holds the current page number. All you've got to do is this:
Code: Select all
<?php
$start = $max * ($page - $max);
$query = "SELECT * FROM table_name LIMIT $start, $max";
?>