[RESOLVED] Performance when paginating large result sets

Chris_96_WS6

The issue is an enterprise-scale web app for a large university...

Among the various tables are two particularly large, one with 16,000 records and another with 10,000 records.

My pagination scheme is typical, run a COUNT(*) query first to determine the total result set and potential # of pages, and then run a SELECT query to return 15/25/50 results from the given starting point.

This all worked well when the # of rows in the tables was < 8,000. At this point page navigation on the larger of the two tables takes 10 seconds for page load, and the other table takes about 7 seconds.

This is not acceptable to me, and I'm sure not to my users either.

Anybody have any alternate strategies, perhaps ones that don't involve returning all results in the first query?

CoderDan

where you have your data collection query, with teh limit, ie:

SELECT * FROM table WHERE filter LIMIT 1000, 100;

or similar, add SQL_CALC_FOUND_ROWS followed by a FOUND_ROWS() query. ie.

SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE filter LIMIT 1000, 100;
SELECT FOUND_ROWS();

naturally, assign a different var for the result of found_rows so it doesn't override your actual results, but the found rows will be how many rows there would have been if the limit was not observed.

any questions?

Chris_96_WS6

Nope, no questions. The new approach cut several seconds off the script execution time, although its still about 6.5 seconds. Thanks.

CoderDan

ok, how fast is it without the SQL_CALC_FOUND_ROWS? cause I don't know of any faster way of counting all the rows, so your next step is to optimize your query.

Chris_96_WS6

Here's the query (queries). Obviously there's a lot of joins here, but the view this is used in displays quite a bit of information. It is also searchable, so the joins need to be there in case search terms cover one of the joined tables.

$query = "SELECT SQL_CALC_FOUND_ROWS
	        Items.itemID
	      , Items.title
	      , Items.date
	      , Items.faculty
	      , Items.author
	      , Items.author2
	      , Items.author3
	      , USER.last_name
	      , USER.first_name
	      , DEPT.dept_name
	      , COMPANY.company_name
	      , TYPE.type_description
	      , STATUS.status_description
	      , certifications.certID
	      , certifications.is_submitted
	      , certifications.cert_type
	      , certifications.hardcopy
	      , certifications.cert_date
	      , certifications.filepath
FROM certifications
LEFT OUTER JOIN Items          ON certifications.itemID    = Items.ID
LEFT OUTER JOIN USER          ON certifications.user_id    = USER.userID
LEFT OUTER JOIN DEPT           ON Items.dept                 = DEPT.deptID
LEFT OUTER JOIN COMPANY    ON Items.Company          = COMPANY.companyID
LEFT OUTER JOIN TYPE           ON Items.type                 = TYPE.typeID
LEFT OUTER JOIN STATUS       ON Items.status              = STATUS.statusID
WHERE Items.deleted = '0'
" . $str . " ORDER BY $sort LIMIT $cursor, $limit ";
$result = mysql_query($query);

// foundrows query
$ct = mysql_query("SELECT FOUND_ROWS()");
$totalrows = mysql_result($ct,0);

Chris_96_WS6

Worth reading: http://www.phpdevblog.net/2009/06/mysql-pagination-sql-calc-found-rows-vs-count-query.html

CoderDan

Well I'd say to make the joins conditional on the search terms, but it looks like every joined table is used in the selected records.
That much of it looks fine, and it looks like you're joining all on IDs which I assume are primary keys, and thus indexed. So it might be in the where filters, which are the only parts not showing here.
How much faster does it run without the SQL_CALC_FOUND_ROWS?
I'm also going to assume that it doesn't put the where part in for a search term that isn't specified

CoderDan

I read your link, the other side of that is it only holds true for the same query, but if you have people searching for different terms every time, that caching doesn't occur, as the query keeps changing.

Chris_96_WS6

You are right that most of the joins, if not all, are used in the table display, so I can't necessarily make them conditional. If no search terms are specified (which is the case in my testing), then no conditionals are included in the $str variable.

There is a negligible difference in performance with SQL_CALC_FOUND_ROWS removed, as in two tenths of a second.

Chris_96_WS6

CoderDan;10922635 wrote:
I read your link, the other side of that is it only holds true for the same query, but if you have people searching for different terms every time, that caching doesn't occur, as the query keeps changing.

I agree, and my testing today has bourne out that FOUND_ROWS() is faster, at least in my circumstances.

After re-evaluating the query, there was one join that was completely unnecessary, and another that I don't have to use because the data is stored in an array which is cached in a text file.

After removing those two joins, I'm down to 4.9 seconds.

CoderDan

ok, so the only place left to trim the fat is in the actual where clauses themselves. (or the mysql server config, or the data types of the fields you collect/filter on)

Chris_96_WS6

CoderDan;10922640 wrote:
ok, so the only place left to trim the fat is in the actual where clauses themselves. (or the mysql server config, or the data types of the fields you collect/filter on)

Server config is out of my hands, I'm living in the "campus" equivalent of a shared hosting environment, although with even fewer access rights.

CoderDan

so, where clauses and table structures. odds are there's a % filter in teh where clause that could be turned into a HAVING ?

Chris_96_WS6

Sure are. I've not used "HAVING" before. Let me see what I can do with that...

CoderDan

actually, that might make things worse, not sure, need to read up a bit 😛

paulnaj

Hi Chris,

I see the thread is marked 'resolved' but it's not clear why - what happened - what it using HAVING? Would be interesting!

Also, I've never actually used OUTER joins before - can I ask why you need them here?

Cheers,
Paul