osCommerce Pagination using FOUND_ROWS

[hava.ge]

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

[hava.ge]

Download is available here: http://addons.oscommerce.com/info/9378

 

See more details more details in the download package.

[♥bruyndoncx]

interesting concept - will have a try (cc @@burt)

[burt]

Yes, interesting concept.

 

@@hava.ge - the code you propose changes too much core.  Can you rework it?

 

Idea:

See if the SQL has SQL_CALC_FOUND_ROWS and if so do an if/else to set $this->number_of_rows using the "new" method or the "old" method.

[hava.ge]

@@burt

Unfortunately, there is no easy way to do it.

I was not able to find a way to detect if SQL has FOUND_ROWS.

The only way, that I found, is to check the version of MySQL.
FOUND_ROWS works since MySQL version 5.0.1.

But, unfortunately, there is another problem:
PHP Function, or mysql query to check the version of MySQL depends on the version of MySQL :)

There are several ways to check the version:

SELECT VERSION();

mysql_get_server_info();

mysqli_get_server_info();

SHOW VARIABLES LIKE '%version%'

SELECT variable_value FROM information_schema.global_variables WHERE variable_name='version'

Witch one to use? All of them (as I read) depends on the server version of MySQL (!!!). Some of them are depreciated, some of them are not supported in the newest versions.

Of course, it is possible, after a heavy coding to detect the version of MySQL, but this is an extra time during pagination on the version check.

Alternatively, I can add configuration key to give an option to the admin, which method to use. The default value will be “traditional method using count()”.

+ I can add admin side checking routine, to check that FOUND_ROWS supported on his server.

 

Is this way acceptable ?

 

 

[burt]

 

if (strpos($this->sql_query, ' SQL_CALC_FOUND_ROWS ') !== false) {
  // new
}
else {
  //old
}

[hava.ge]

I thought you wanted to check whether supported or not FOUND_ROWS in the mysql server version.
 

if (strpos($this->sql_query, ' SQL_CALC_FOUND_ROWS ') !== false) {
  // new
}
else {
  //old
}

I don't understand why to go this way, when there is an easy way to add (or not) last parameter to the splitPageResults function call, and set this parameter 'true'.
... and no need to change all queries where is used pagination.

just add last parameter 'true' to the function call, and contribution will do all work.

.... = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 
                            'p.products_id', 'page', true);

If the user don't need new concept, all will work by oscommerce core code, nothing need to change, nothing to worry about.

If the user needs new concept, he will add last parameter 'true'.

[♥14steve14]

@@hava.ge

 

I think what is being said is that the new direction oscommerce is heading in, is that core code changes should be kept to a minimum, so that it is easy to update stores and code. This means that use should be made of modules and the proposed hooks system, eliminating core code changes. All new addons should really try to use this system keeping the coding standard throughout the whole addons area. v

 

Gary is saying that you may need to rethink your addon approach as there are other ways of achieving the same result with no or minimal core code changes.

 

If I have this wrong I am sure that someone will point it out and correct me.

Edited October 22, 2015 by 14steve14

[burt]

@@hava.ge

 

1.  there are hardly any places that use Pagination

2.  not all pagination uses product_listing

 

Hence...you need to change the pagination SQLs anyway.

So do the processing in the SPR class, based on the content of the SQL call.