Whatterz


A Quick Way to Help Optimise MySQL Databases

by Simon. Average Reading Time: less than a minute.

Generally, if you want to analyse a MySQL table you can use the following command.

ANALYZE TABLE table_name

However, this doesn’t give you much information beyond “status=OK”.

Alternatively, the ANALYSE() procedure built into MySQL can give you a wealth of information about your database tables, making it easy to find out which tables need optimising.

SELECT * FROM table_name PROCEDURE ANALYSE()

NB. change table_name to be your desired table name…obviously! Oh and “analyse” is the English (UK) spelling.

You’ll get a number of results back including the minimum/maximum value, minimum/maximum length, number of empty or zero strings, number of nulls, the average value/length, the standard deviation, and a suggested optimal field type for every row in the queried table.

Of course, optimising your tables isn’t necessarily that helpful if you don’t have much data. Furthermore, optimisation isn’t simply a case of running the OPTIMIZE TABLE function on each of your tables.

This article has been tagged

, , , , , , ,

Other articles I recommend

SQL User-Defined Function: ListToTable

A common need in SQL is the ability to iterate over a list as if it were an array. In SQL it is not possible to declare arrays, unlike other programming languages such as ColdFusion, ActionScript and Java. Fortunately, there is a way around this problem: use a User-Defined Functions (UDFs) to create a tabular version of the data. Arrays are, after all, essentially tabular data (at their simplest, one dimension level).

ColdFusion ORM Frameworks & Tools

Object-Relational Mapping (or ORM), is a programming technique that links databases to object-oriented language concepts, creating (in effect) a “virtual object database”. There are both free and commercial packages available that perform object-relational mapping, although some programmers opt to code their own object-relational mapping for their systems.

How to Protect Your Website from a Malicious Attack

Every seasoned developer will know that protecting your website from a hacker is a top priority, whether for your own reputation or for maintaining your company’s reputation and log-term revenue prospects.