9 Tips To Speed Up MySQL Tables
In designing my content enriched website, I quickly discovered that trying to handle large quantities of data is a real bottle neck that can be a pain to overcome.
So here’s a few (unorganised) tips that I think will help:
- Don’t make a VARCHAR field 255 characters if it’ll only ever be 32 characters long.
- Use UNSIGNED where possible (that’s anywhere you’re not going to store a negative value).
- Use the smallest field types possible, if your range doesn’t exceed 0~255 then TINYINT is your best friend. Applying these basic tactics to all fields will help reduce the size of your table and ultimately reduce the overall workload that MySQL has to deal with.
- Split large data into other tables. If you have a LONGTEXT in your table, you’ll find that disk fragmentation causes hefty disk work. To help prevent this you can move the LONGTEXT fields into a table on their own. We’ll call the first table the ‘log’, and the second table the ‘data’. You store all the basic information in the ‘log’ table (such as the title, date and size), and then the content in the ‘data’ table.
- In addition to point 4- if you have way too much data, try making multiple ‘data’ tables fed by the same ‘log’ table, this makes optimization much easier.
- If you’re using PHP or something similar, you can usually use sessions to store data that is frequently used rather than requesting it from the database.
- In addition to point 6 – if you have many members accessing the same data, sessions just won’t cut it. But installing and using MEMCACHED might – it gives you a read/write cache by using some of your spare computer memory.
- Always make sure you make use of your indexes properly.
- Never allow indexes that aren’t used to remain active.