Can I optimize this process?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Can I optimize this process?

Post by seodevhead »

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.
User avatar
Oren
DevNet Resident
Posts: 1640
Joined: Fri Apr 07, 2006 5:13 am
Location: Israel

Post by Oren »

Fot counting the rows use something like this:

Code: Select all

SELECT COUNT('field_name') FROM table_name
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

<?php

	$start = $max * ($page - $max);

	$query = "SELECT * FROM table_name LIMIT $start, $max";
?>
Post Reply