SQL command to del the customer orders record from year 2006 to 2010

[explorer1979]

Hi all,

I have a live shop the customers record have over 3000 pages

It make the back end show the orders detail slowly down, since I am copy the web site to a test server and monitor what it doing when I click the orders, the mysqld eat up 50% CPU power each time I do this step...

So, now I am thinking del the older records from year 1st Jan 2006 to 31st Dec 2010. BUT KEEP ALL the RECORD from 1st Jan 2011 to now.

I can full access the server by cPanel with phpmyadmin, but I am don't know how to write the SQL command do like above.

Anyone can help me of the SQL command?

Thank you very much of your time and reply.

[RodG]

I have a live shop the customers record have over 3000 pages

This is well within the limits of what ZenCart can handle.

It make the back end show the orders detail slowly down, since I am copy the web site to a test server and monitor what it doing when I click the orders, the mysqld eat up 50% CPU power each time I do this step...

So, now I am thinking del the older records from year 1st Jan 2006 to 31st Dec 2010. BUT KEEP ALL the RECORD from 1st Jan 2011 to now.

If you did this, I think you'll just be hiding the symptoms rather than solving the actual problem.

I can full access the server by cPanel with phpmyadmin, but I am don't know how to write the SQL command do like above.

Anyone can help me of the SQL command?

Thank you very much of your time and reply.

Deleting records via SQL commands like this is not an easy task and a very risky thing to do because records from one table will refer to the key fields of another table, and deleting records without taking this into consideration will lead to a very corrupt database.

Just out of curiosity, exactly when did the slowdowns start? This kind of behaviour can often be traced back to a faulty mod installation (or in some cases, just a badly coded mod).

Have you tried running a 'repair' on your database?

Any other oddities worth noting?

The bottom line of what I'm trying to tell you here is that you seem to have come to a conclusion that the problem is related to the number of customer records, and that I feel this is a premature conclusion, and that there are many other things that are more likely to be causing the slowdowns.

Do you *really* want to delete all of this potentially valuable historical data, based on a hunch? (or do you have another reason to suspect that the quantity is the cause?)

Cheers
Rod

[explorer1979]

RodG,

First of all, thank you of your detail answer. And suggestion.

I have this thinking since when I click any order in the "Super Order", yes, I had install some of MOD in the Zen Cart, one of it is the "Super Orders 3.0.2", and whatever I click any order on it, the IE or Firefox page need waiting about 5s or above to show the content or response.

Yes and I had anyother Zen Cart 1.3.9h, same mod on other domain for different products, but this shop is little orders there. Same class server. BUT this shop just a click then 1 second response, not same as the 3000 page shop need few seconds.

So it is why later I am using hosting company's cPancel phpmyadmin to full backup the MySQL DB, then restore it to my NoteBook with 8G ram on Windows WampServer run the same testing, I open the Windows Resource track the running LIVE, find "MYSQLD" CPU load is over 50% few seconds like above I while I click, this way, I can know that mysqld eat up the CPU running something cal of the table when I click....

So .... you have more detail inform how my test. Do you have any other thinking on this?

Again, thank you of your time and kind help.

[kobra]

Enable parse time display in your admin > config > logging > Display The Page Parse Time

What is your server load?

admin > tools > server/version info > then check "Server Up Time:" & " load average:"

Provide a link to your site

[RodG]

RodG,

First of all, thank you of your detail answer. And suggestion.

I have this thinking since when I click any order in the "Super Order", yes, I had install some of MOD in the Zen Cart, one of it is the "Super Orders 3.0.2", and whatever I click any order on it, the IE or Firefox page need waiting about 5s or above to show the content or response..

First and foremost. Does the problem go away if you don't install the Super Orders module? It is going to be pointless looking for a 'zencart' problem, if the problem turns out to be related to this (or another) add-on.

So it is why later I am using hosting company's cPancel phpmyadmin to full backup the MySQL DB, then restore it to my NoteBook with 8G ram on Windows WampServer run the same testing, I open the Windows Resource track the running LIVE, find "MYSQLD" CPU load is over 50% few seconds like above I while I click, this way, I can know that mysqld eat up the CPU running something cal of the table when I click.....

The reason why is simple. Different data = different results.

Do you have any other thinking on this?

I can't/won't argue your observations that the number of records is a factor, but at the same time, I can't ignore the fact that the problem isn't 'typical' of a ZenCart installation. I suspect the cause is a combination of the Super Orders module *and* the number of records in the database. Sadly, this doesn't really lead to a proper solution though, because the combination itself could be just enough to trigger an overload problem with the server itself. In short, this one 'symptom' *may* be 'cured' in at least 3 different ways (Disable the Super Orders module, reduce the number of records, or increase the server capacity). None of these actually solving the problem, all just hiding the symptoms (but this may be the best you are able to achieve in this scenario).

Cheers
Rod

[explorer1979]

Enable parse time display in your admin > config > logging > Display The Page Parse Time

What is your server load?

admin > tools > server/version info > then check "Server Up Time:" & " load average:"

Provide a link to your site

Server Up Time: 11:16:25 up 383 days, 17:11, 0 users, load average: 0.41, 0.42, 0.36

[explorer1979]

Hi all,

I still try to troubleshooting this case. Maybe I provide more detail screen capture helpful for any expert here look for the problems.

The problems server is own by my company, and it only run one host one web site only, it spec is
1x Xeon 3060 2.5GHz CPU
2G DDR2 RAM
320G 7.2K SATA x3 running RAID 5
1U Original Intel Server, which model unknown, not buy from me.

Fedora OS
PHP 5.2.5 (PHP Memory Limit: 256M)
MySQL 5.0.45 (MyISAM Table and Enginne)
Apache 2.2.9
cPanel
load average: 1.6, 1.5, 1.00 (Off Peak only, if on US Time, it will not this value, I try to capture this later)











If need more infrom just ask for me that I can provide if can help for fix it!
And some questions, do all shop you using also using MyISAM Engineer? Or change to InnoDB, MySQL 5.1 or 5.5? Anyone do different like that etc?

Thank for your time and help.

[explorer1979]

[explorer1979]

[explorer1979]

Server Up Time: 21:16:12 up 398 days, 3:07, 0 users, load average: 2.10, 3.11, 3.18