For the last couple of weeks I’ve been talking about indexes and how they help speed up database searches. I talked about their pros and cons and then talked about the different types of indexes and when each is appropriate.
As useful as indexes are to finding information in a database, they aren’t all created equal. Sometimes you need to find specific values in a table and sometimes you need to find something inside a specific value, say a blob of text. Not all indexes are suited for every task.
It takes time for a database to search through row upon row of information and find specific values. It’s a task made more difficult as rows of data are removed and new ones are added. Over time, data can be stored more randomly than you would think or like. Indexes are a way to overcome this and to help make it quicker to find values in database tables.
Sometimes the defaults that come with a product are exactly what you want. Other times they aren’t and you need to make a few changes. When it comes to MySQL, you’ll probably get along just fine if you never change a thing, but you can probably do better than the defaults with a few tweaks.
It takes time to open a connection to a database, make a query, have the database find the requested data, and send it back to the file that made the request. That’s true even for very simple queries. The time depends on how the database is configured, the hardware on the server, how the data is stored in the tables being searched, and of course, how well the queries have been written.