1. Home
  2. Support
  3. Scripting
  4. MySQL Database house keeping

MySQL Database house keeping

Database Optimisation

From time to time your database may seem like the size is getting closer and closer to the quota limits but you’ve not added or changed anything for a while.

This is mostly caused by indexing and caching within the SQL database. There is a simple solution to this, it’s called Table Optimisation. You can perform this task from within phpMyAdmin. For shared hosting customers, you can find the link here.

Let’s break this down a bit.

  1. Log into phpMyAdmin (your user will require the DBA permissions)

    You can find which phpMyAdmin you need by going to Databases -> MySQL -> MySQL DBs on the Control Panel

    The phpMyAdmin link can be found on the databases page.

  2. On the tree menu on the left hand side, select the database you’d like to look at.
  3. Scroll to the bottom of the page and look for the “Check All” option, this will tick all of the tables in the database.
  4. Find the drop menu which will say “… With Selected” and select “Table Optimisation” then press go.

Note: Please take care when working with live databases. There are some quite destructive options on that list.

You’ll find that the overall size of the database may reduce as it will clear out any overhead data in the database which is no longer required. Think of it as similar to clearing out your temporary internet files on your computer.

Database user permissions

With the ability to add more than one user to a single database, you will make sure that your website uses a user which only has the read/write permissions. You can then create another user, preferably with a strong password to have the dba permissions. This user will only require the DBA Permission, when accessing phpMyAdmin.

This means that if for some reason a website becomes compromised the attackers won’t have complete full control over the database and cause more damage than they already have.

Click here for full details

Classification: Public
Last saved: 2019/08/16 at 13:58 by Jamie