Jump to content
hava.ge

osCommerce Pagination using FOUND_ROWS

Recommended Posts

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


My addons:

Content Templates Manager :  http://addons.oscommerce.com/info/9288

Admin side horizontal no javascript css menu: http://addons.oscommerce.com/info/8830

osCommerce Pagination using FOUND_ROWS: http://addons.oscommerce.com/info/9378

 

 

 

 

Share this post


Link to post
Share on other sites

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

 

See more details more details in the download package.


My addons:

Content Templates Manager :  http://addons.oscommerce.com/info/9288

Admin side horizontal no javascript css menu: http://addons.oscommerce.com/info/8830

osCommerce Pagination using FOUND_ROWS: http://addons.oscommerce.com/info/9378

 

 

 

 

Share this post


Link to post
Share on other sites

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


KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Share this post


Link to post
Share on other sites

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.


Help shape the future of Phoenix; join the Phoenix Club

Share this post


Link to post
Share on other sites

@@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 ?

 

 


My addons:

Content Templates Manager :  http://addons.oscommerce.com/info/9288

Admin side horizontal no javascript css menu: http://addons.oscommerce.com/info/8830

osCommerce Pagination using FOUND_ROWS: http://addons.oscommerce.com/info/9378

 

 

 

 

Share this post


Link to post
Share on other sites

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'.


My addons:

Content Templates Manager :  http://addons.oscommerce.com/info/9288

Admin side horizontal no javascript css menu: http://addons.oscommerce.com/info/8830

osCommerce Pagination using FOUND_ROWS: http://addons.oscommerce.com/info/9378

 

 

 

 

Share this post


Link to post
Share on other sites

@@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 by 14steve14

REMEMBER BACKUP, BACKUP AND BACKUP

Get the latest Responsive osCommerce CE (community edition) here

It's very easy to over complicate what are simple things in life

Share this post


Link to post
Share on other sites

@@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.


Help shape the future of Phoenix; join the Phoenix Club

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×