Sly
Admin
6184
June 2002
|
 |
June 10 2003,13:07 |
|
TITLE: SQL table optimisation using phpMyAdmin - a small tutorial by Sly
Background:
If you think or your web host is reporting that your database seems 'very large' considering the number of posts you have, then this could be just the thing you need.
A real case:
Today one user was reporting his database was greater than 50Meg in size  and thats pretty big considering they were pruning regularly. After optimiseation the database was down to 18.7 Meg, thats a greater than 50% improvement on space used.
Cause:
This is due to the way the data is organised in the database and the way the data is deleted from it by the SQL itself. This means that often the table gets filled up with old data that will either be overwritten automatically or be left behind and cause 'bloating' of the database.
Solution:
In order to check and optimise the database, go into phpMyAdmin and look at each table individually:
 {this.width = (this.width/2)}" onclick="javascript:if(this.width > (screen.width/2 {this.width = (this.width*0.5)} else {this.width = (this.width*2)}" border="0" alt='Posted image: Click to resize'>
If the table needs optimising then phpMyAdmin will show this.
Normally the display at the bottom will look like:
 {this.width = (this.width/2)}" onclick="javascript:if(this.width > (screen.width/2{this.width = (this.width*0.5)} else {this.width = (this.width*2)}" border="0" alt='Posted image: Click to resize'>
but a table that needs optimising will look like:
 {this.width = (this.width/2)}" onclick="javascript:if(this.width > (screen.width/2{this.width = (this.width*0.5)} else {this.width = (this.width*2)}" border="0" alt='Posted image: Click to resize'>
in that case click the Optimize table link and the rest will be done by phpMyAdmin.
You need to do this for all the tables in the database, and these can either be done individually, as shown here, if you are interested in which are the problem tables, or it can be done 'en mass' as shown below as part of a maintenance routine.
 {this.width = (this.width/2)}" onclick="javascript:if(this.width > (screen.width/2{this.width = (this.width*0.5)} else {this.width = (this.width*2)}" border="0" alt='Posted image: Click to resize'>
MAKE SURE YOU SELECT THE OPTIMISE OPTION!!
else you could be in big trouble.
[ iB Supported home ]
... END OF LINE
|
