This contribution was created to improve osCommerce pagination class performance by avoiding two slow queries, and using SQL_CALC_FOUND_ROWS technique for pagination.
Using this contribution you can increase pagination process performance two times.
some information:
As you know (maybe you do not know), in the splitPageResults class the pagination process realised using two separate SQL-queries: - The first query "select count(...)", to count how many records will return the main query. - The second query - the query with " limit offset, number_of_rows_per_page", to select results and limit number of rows.
Performance is good when the main query has no "where" clause, or "where" clause based on an indexed field.
The performance problems occurs when products table become huge and there is a where clause with a search keywords. Performance has fallen dramatically. This occurs because the: 1. "select count(...)" query have to select all resulting records to calculate amount of records. It takes an extra time because of the "select count" query is slow when there is a "where clause" on the non indexed fields. 2. Then, the second query will do the same job - select records using limit. Two queries does the same job twise. As a result, we spend double time. (for example, in my case, it spends 4.4 seconds on the "select count(...)" query, and then it spends 4.7 seconds on the second query. Both queries take 9.1 seconds).
SQL_CALC_FOUND_ROWS technique allows to decrease this double time (in my case, it decreased to 4.4 seconds). This technique is well known, and is realised in this contribution.
Compatibility:
I'm using this contribution on the osCommerce v2.3