Index your data

Database indexes allow MySQL to store a look-up table of values in its tables that it can use to locate data much quicker than having to search every record. You create an index using the command "ALTER TABLE... CREATE INDEX name(column)" - the name is just a name for your reference, but the column should be the name of the row you want to construct an index of.

Consider the following schema, from before:

CREATE TABLE mydata (ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Username CHAR(255), Age TINYINT UNSIGNED, JoinDate INT, Homepage CHAR(255), Location CHAR(255), FaveColour CHAR(255), Password CHAR(255), PassRemind CHAR(255))

Now, after adding 200,000 records to that, of which the middle one (around about #100,000) had the Username TelRev, the following query took 0.66 seconds to executed:

SELECT ID FROM mydata WHERE Username = 'TelRev' LIMIT 1;

Given that we're dealing with just shy of a quarter of a million records, 0.66 seconds might not seem all that bad. However, we can speed that by adding an index with this command: ALTER TABLE mydata ADD INDEX idxusername(username).

Adding an index will take a little time to execute, and also subsequent inserts/updates will be a mite slower because of the need to update the index, but it is worth it - executing the same SELECT query again now takes 0.00 seconds. Yes, you read that right - it executes so quickly MySQL does not even register the time it took.

You can add indexes to as many columns as you want, and you can even index every column if you like. However, there are trade-offs, as per usual - indexes take up space, so the more indexes you have, the larger your table will be. Secondly, each time you change a row, you also need to change the index - if you index every column you will need to update several indexes to reflect the changes. If you have a table that is written to exponentially less than it is read from, you can go ahead and try adding as many indexes as you think necessary.

It is ***NOT*** recommended that you add an index to a column that has a lot of repetition across rows. For example, indexing addresses is fine, because most people have a unique address. But indexing "male" and "female" for their gender is pointless, because there are only two possible values.

If you decide at a later date you would rather not have an index, use this command: ALTER TABLE <table> DROP INDEX <index name>.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.