MySQL queries still slow after deleting bunch of records

All we need is an easy explanation of the problem, so here it is.

I need some help with resolving some mysql issues. My website had been running slow since last week and after contacting my host I found out that some queries were taking too long mainly because of table locks. I am a developer but no mysql/database expert. My host suggested that I either delete and/or change the two tables in question to innoDB. So as these tables had a lot of junk data I decided to delete a bunch of records. I would say that these two tables are about 25% of the size when this problem started. Problem is that, it still has not made any difference. So my questions:

  1. Do I need to clear cache or optimize table in order to see the
    effects? My host is still suggesting I change those tables to innoDB
    which is fine but I’m not sure why deleting that many records have
    not made a difference.
  2. Also I have read that it is better to
    recreate the table than just optimizing? If needed I can hire a
    database admin to help me but I would like to at least try a few
    things if this is something simple. Can someone please guide me
    through this.

One more important thing to add, it is a legacy website running on php 5.4 and mysql 5.6

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Rather than change a pair of tables to InnoDB, it would be better for every table in the database to be converted as InnoDB is a much better storage engine. That said, MyISAM is workable if you don’t mind babysitting it every once in a while.

Do I need to clear cache or optimize table in order to see the effects?
⇢ The database engine will clear the cache on its own, but you can force this by restarting the server. Optimising the tables is certainly recommended for MyISAM tables if you haven’t done so in a while. This will ensure that the data is in a consistent state and that the indexes are properly linked.

Also I have read that it is better to recreate the table than just optimizing?
⇢ It is sometimes faster to recreate than optimise. If you plan on tackling just the indexes, then DROP and CREATE are your friend. If you want to also check the integrity of the tables, then OPTIMIZE TABLE is what you’re looking for. If your site doesn’t get too much traffic, you could even look at dropping the index, optimising the table, then recreating the index.

Ideally this should be done for every table in the database, not just the two in question. Databases, like many other things, need the occasional bit of maintenance to remain reliable.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply