Database Optimizer

[Jack_mcs]

This addon provides an easy method for keeping your database fit. All osCommerce shops require a MySQL database to function but little attention is ever given to it. Just ask yourself when was the last time you optimized your database? Unfortunately, this is a big mistake since MySQL is prone to developing problems. Some are fixed as the database runs but some are not and, over time, these grow and can severely effect your shops performance. This addon will automatically tune-up the database, speeding it up and correcting problems that many shop owners are not even aware of. Here are some of its features:

 

• Defragment the tables
  
• Optimize the keys
  
• Strip the customers tables of old data
  
• Strip the sessions table of old data
  
• Strip the user tracking table (if present) of old data
  
• Remove old credit card numbers
  

Edited February 6, 2011 by Jack_mcs

[Guest]

I like this contribution, been looking wanting this for awhile now. Thank you, Jack. One question, what do you mean you need cron job? My host does not provide the cron job feature. Can I still use this contribution?

[Guest]

I like this contribution, been looking wanting this for awhile now. Thank you, Jack. One question, what do you mean you need cron job? My host does not provide the cron job feature. Can I still use this contribution?

 

 

EDIT:

 

Tested. All working! You do not need to have cron jobs to make this mod work but it helps. For the future, it would be nice to know what is being optimized and how. Seeing the numbers, I don't really know if its working or not. Just seeing filesize going down.

[Guest]

This addon provides an easy method for keeping your database fit. All osCommerce shops require a MySQL database to function but little attention is ever given to it. Just ask yourself when was the last time you optimized your database? Unfortunately, this is a big mistake since MySQL is prone to developing problems. Some are fixed as the database runs but some are not and, over time, these grow and can severely effect your shops performance. This addon will automatically tune-up the database, speeding it up and correcting problems that many shop owners are not even aware of. Here are some of its features:

 

• Defragment the tables
  
• Optimize the keys
  
• Strip the customers tables of old data
  
• Strip the sessions table of old data
  
• Strip the user tracking table (if present) of old data
  
• Remove old credit card numbers
  

 

Thanks Jack,

I optimized my database and the result is great:

Store database has been optimized.

Database was optimized.

Initial size was 26809745.

Final, optimized, size is 5486202!!

Regards,

Martina

[Jack_mcs]

EDIT:

 

Tested. All working! You do not need to have cron jobs to make this mod work but it helps. For the future, it would be nice to know what is being optimized and how. Seeing the numbers, I don't really know if its working or not. Just seeing filesize going down.

It really needs to be ran regularly, even if twice a year, and cron is the best way to do that. If your host doesn't offer cron, you can use my Cron Simulator contribution.

 

As for what it does, an explanation of each item is given in the admin. The numbers will vary with each database. For example, if you don't store credit card numbers in your shop and only run that option, the script will always say it ran but nothing will have been changed since there isn't anything to change, in that case.

[Jack_mcs]

Someone pointed out to me that there is a mistake in the install instructions for 2.3. It should read:

FIND:

 

array(

'code' => FILENAME_BACKUP,

'title' => BOX_TOOLS_BACKUP,

'link' => tep_href_link(FILENAME_BACKUP)

),

 

ADD BENEATH:

 

array(

'code' => FILENAME_DATABASE_OPTIMIZER,

'title' => BOX_TOOLS_DATABASE_OPTIMIZER,

'link' => tep_href_link(FILENAME_DATABASE_OPTIMIZER)

),

Edited February 6, 2011 by Jack_mcs

[johnnybebad]

Hi I havent installed this mod yet, but have no doubts it would be useful as it comes from Jack.

 

my query is, what does the optimisation do exactly.

 

If I ran the optimization, things are being changed but what exactly is being changed.

 

sorry for souning thick but I am trying to work out what things would change.

 

ie if rearranging the tables so they are sequential and use up the numbers in order ie no gaps, in config I have 500 rows but my next entry would be 6473, it renumbers it 1 to 500? next entry being 501.

 

this would then in the products table make the entries change if I have delted any old products(discontinued never to be seen again) so my urls for acessing them would change so i need to update my sitemaps, googlebase etc etc.

 

Am I thinking along the correct lines, or way off, just want to get thiongs straight in my head before using this one.

 

Cheers

[Guest]

Hello Jack,

Id like to ask what this feature does in more detail...

# Strip the customers tables of old data

 

What data is stripped from the customers tables?

[Jack_mcs]

Hi I havent installed this mod yet, but have no doubts it would be useful as it comes from Jack.

 

my query is, what does the optimisation do exactly.

 

If I ran the optimization, things are being changed but what exactly is being changed.

Nothing is being changed as far as your shop is concerned. The optimization doesn't refer to rewriting some table to it operated more efficiently, or anything like that. The optimize and analyze functions are functions built into mysql to perform certain housekeeping functions. They are never ran from with oscommerce so they never, in most case, get ran. The other items are removing old entries from the tables. Those are entries you will never need. For example, the sessions table contains tracking information for your customers. If you have had your shop open for five years, then it contains tracking information for five years worth of customers, unless it has been edited during that time. Do you really want to store tracking information for a customer from five years ago? Even if that customer was still active on your site, or even alive for that matter, your site has probably changed so it may not even be accurate. The sessions option will remove all of those old entries. Where it starts deleting them depends on the settings in admin. The same is true for the other options.

[Jack_mcs]

Hello Jack,

Id like to ask what this feature does in more detail...

# Strip the customers tables of old data

 

What data is stripped from the customers tables?

Nothing from the customers table. The customers_basket and customers_basket_attributes get emptied of old data. They are similar to the sessions table in that they store what a customer has in their cart. There's not any reason to store that information for more than a month, if that.

[MHWD]

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\catalog\admin\includes\modules\database_optimizer_common.php on line 59

 

Not sure why this is being thrown - only happening on optimize and analyze obviously. Any ideas what would cause this?

[Jack_mcs]

Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\catalog\admin\includes\modules\database_optimizer_common.php on line 59

 

Not sure why this is being thrown - only happening on optimize and analyze obviously. Any ideas what would cause this?

That error would indicate it is failing due to the SHOW TABLE STATUS mysql call. I can't think of why that would fail unless you are using a very old version of mysql, or maybe some buggy version, though I can't find reference to such a problem.

[MHWD]

That error would indicate it is failing due to the SHOW TABLE STATUS mysql call. I can't think of why that would fail unless you are using a very old version of mysql, or maybe some buggy version, though I can't find reference to such a problem.

 

I couldn't find a reference either, it's on xampp with PHP Version: 5.3.1 & MySQL 5.1.41.

 

In addition - if I run the SHOW TABLE STATUS in the sql query it works with no problems.

 

 

Thank you though

Edited February 8, 2011 by MHWD

[bgagew]

This addon provides an easy method for keeping your database fit. All osCommerce shops require a MySQL database to function but little attention is ever given to it. Just ask yourself when was the last time you optimized your database? Unfortunately, this is a big mistake since MySQL is prone to developing problems. Some are fixed as the database runs but some are not and, over time, these grow and can severely effect your shops performance. This addon will automatically tune-up the database, speeding it up and correcting problems that many shop owners are not even aware of. Here are some of its features:

 

• Defragment the tables
  
• Optimize the keys
  
• Strip the customers tables of old data
  
• Strip the sessions table of old data
  
• Strip the user tracking table (if present) of old data
  
• Remove old credit card numbers
  

[Lioelx]

hi,

need help for this error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/web09c/b653/moo.nukuaes/tienda/admin/includes/modules/database_optimizer.php on line 43

 

Warning: mktime() expects parameter 6 to be long, string given in /hermes/web09c/b653/moo.nukuaes/tienda/admin/includes/modules/database_optimizer.php on line 45

[Jack_mcs]

hi,

need help for this error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /hermes/web09c/b653/moo.nukuaes/tienda/admin/includes/modules/database_optimizer.php on line 43

 

Warning: mktime() expects parameter 6 to be long, string given in /hermes/web09c/b653/moo.nukuaes/tienda/admin/includes/modules/database_optimizer.php on line 45

The second error is most likely due to the first. For the first, try changing line 43 in admin/includes/modules/database_optimizer.php

$mainDate = mysql_fetch_array($query, MYSQL_ASSOC);

to

$mainDate = mysql_fetch_array($query, MYSQL_BOTH);

or

$mainDate = mysql_fetch_array($query);

Edited February 10, 2011 by Jack_mcs

[Lioelx]

Hi, I tried the changes you told me but still leaving the same error, check the box to analyze the database and displays the same mistakes.

Greetings

View PostLioelx, on 10 February 2011, 04:42, said:

 

The second error is most likely due to the first. For the first, try changing line 43 in admin/includes/modules/database_optimizer.php

 

$mainDate = mysql_fetch_array($query, MYSQL_ASSOC);

 

to

 

$mainDate = mysql_fetch_array($query, MYSQL_BOTH);

 

or

 

$mainDate = mysql_fetch_array($query);

 

[Jack_mcs]

Hi, I tried the changes you told me but still leaving the same error, check the box to analyze the database and displays the same mistakes.

Greetings

I don't have an answer for you then. The code is common code. All I can think of is that there is something different in the version of php/mysql your host uses. You could try asking your host about it though most won't look at third-party code.

[martin67]

I don't have an answer for you then. The code is common code. All I can think of is that there is something different in the version of php/mysql your host uses. You could try asking your host about it though most won't look at third-party code.

 

I had the same problems as Lioelx. In my case it was that I used a different name for the database_optimizer table than the default. This is really a (minor) bug in the code.

 

In admin/include/modules/database_optimizer.php, change line 42 from

 

$query = mysql_query("select last_update from database_optimizer");

to

$query = mysql_query("select last_update from " . TABLE_DATABASE_OPTIMIZER);

 

In the same way, change all references (5) to database_optimizer in admin/include/database_optimzer_common.php

 

 

Jack_mcs, maybe you can fix this in the next release?

 

Regards,

/Martin

[Guest]

Hi Jack,

 

Thank you another great contribution :-)

 

Does the version checker you created work in 2.3.1?

 

Cheers

[Jack_mcs]

Hi Jack,

 

Thank you another great contribution :-)

 

Does the version checker you created work in 2.3.1?

 

Cheers

Yes, it does.

[Guest]

Cool, I will download it and install it today

 

Cheers

[Cheepnis]

I think this is a great mod, but a much more complete description of each setting in the configuration and what exactly is removed/truncatedwould be a big help in determining what length of time to store old data. Not only would it help those of us who are curious about what's under the hood, it would certainly make those who are squeamish about installing a mod that directly affects their database feel more comfortable about installing it.

Thanks!

[Jack_mcs]

I think this is a great mod, but a much more complete description of each setting in the configuration and what exactly is removed/truncatedwould be a big help in determining what length of time to store old data. Not only would it help those of us who are curious about what's under the hood, it would certainly make those who are squeamish about installing a mod that directly affects their database feel more comfortable about installing it.

Thanks!

Yes, documentation is always a problem and the least attractive thing to do to a coder. But short of that, I suppose you could go all the way back to page one and read the description there.

[Cheepnis]

I recently had a customer concerned that his new shipping/billing addresses that he updated to a while ago have reverted back to the old addresses for some reason. He corrected them, placed his new order, then when the package didn't arrive in the mail, he looked and saw the addresses had again reverted back to the old. The only thing I can think of that might cause this behaviour (and I have performed no database restores) is this mod, as I have been running it somewhat regularly. Is it possible that there's a bug somewhere here with the customer data cleaning? Another reason why it might be good to have a more detailed description of what exactly this mod does. :)

Edited April 27, 2011 by Cheepnis