What is MySQL Indexing?
Many modern websites rely on databases to store important data. When these databases get large, your server can slow down when there are a lot of requests (queries) that the database has to handle. Speeding these queries up requires knowledge of MySQL indexing.
Most of the time, your content management system (CMS) will handle all necessary MySQL indexing for you. You will rarely have to set up an index on your own. If your website and database structure is completely customized, you will most likely have a database administrator on staff to handle all your indexing needs.
But, it’s helpful to have a basic understanding of how queries, sorting, and indexes work in MySQL. Using indexes sorts your data to run queries faster and use fewer server resources.
When your server queries a database and there are no indexes, you can’t be sure if the data is in any kind of order (“sorted”). You have to start at the top of the database and look at every record to see if it matches what you’re looking for. For example, what if there are a million records and the record you need is the very last one? You have to read through the data a million times to find what you need. This is called linear searching.
Linear searching takes a long time and a lot of resources. But if your data is sorted using an index, you can sort your data into groups. Once your data is sorted, your server can use binary searching to increase efficiency.
Binary searching works like a flow chart. Instead of starting at the top of the list, binary searching starts in the middle. Let’s take a list of prime numbers as an example:
2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47
These numbers create our data set, and we are going to look for the number 37. They are sorted from the lowest number to the highest number, so we can use binary searching.
To start binary searching, take the middle number, 19. Now the MySQL database asks: is 37 higher or lower than 19. The answer to that question is higher. This means you can eliminate 19 and all records before 19 from your search. You’re left with half the data to search:
23, 29, 31, 37, 41, 43, 47
Now, pick the middle number in your new data: 37. Ask the same question: is 37 higher or lower than 37? It’s exactly 37! You’ve found the data you need after only two questions (also called reads). If you had used linear searching, you would need 12 reads to get the same information.
Indexes take advantage of binary searching and take it one level further. They store common sorted data sets for you, just like the index of a book. If a term in a book has five pages listed, you only have to look through those five pages to find what you’re looking for. In MySQL indexes, there are lists of relevant records, sorted for binary searching.
Again, you will rarely, if ever, have to deal with MySQL indexes. If you use a common content management system like WordPress, all the indexing is already in place behind the scenes. If you’re using a custom solution, your database administrator will take care of it for you.
