Brian2000

Database Tips: Indexing Is Essential!

Indexing can make or break a database. Queries that selects with multiple criteria are often not indexed at all and they need to be! If you were to query a large table for a specific record, having the record’s criteria handy is not going to save you any time unless you directly query the record’s index. The larger a database is the slower you are going to get results from a non-indexed query. Creating a few helpful indexes that coincide with your applications queries is not only a life-saving measure, it's essential!

For this post I am going to use the following example MySQL table; however these concepts apply to all database flavors.

CREATE TABLE IF NOT EXISTS `records` ( `recordID` int(11) NOT NULL AUTO_INCREMENT, `recordName` int(11) NOT NULL, `recordDate` int(11) NOT NULL, `recordValue` int(11) NOT NULL, PRIMARY KEY (`recordID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

If I were to query a record with SELECT * FROM records WHERE recordID = 10 the results would search the primary key index since recordID is the primary key. This would return results quickly regardless of how large the table gets (since indexes don’t have to scan through every single row).

However, with the query SELECT * FROM records WHERE recordName = 'Record A' the database would have to scan every row for a match. In order to avoid this you need to index the table properly. The best way of indexing a database is by understanding what queries are going to be used. In this example an index for recordName would have worked.

If you were to perform a more complicated query though, that query would also need to be indexed. For example: SELECT * FROM records WHERE recordID=10 AND recordName = 'Record A' is not going to search via the indexing described above. Yes, there is an index on recordID and the previous example called for an index on recordName. The problem with having indexes on individual fields is that the database understands these as separate indexes. So queries that combine multiple fields (even fully indexed fields) do not combine the existing indexes for the operation. Therefore in order to use indexing on a query with multiple fields like this one you need to build an index for all fields being searched. The proper index for this example would be a combination recordID and recordName index.

I think the biggest thing that is overlooked with table indexing is that application developers do not communicate with database developers, or when the database is constructed the queries that are being run by the application have not been sorted out yet. To avoid this I recommend developers keep a running list of all queries they are running. If you aren’t in charge of the database communicate this list with the database administrator. If you are in charge of the database, make it your business to create proper indexes. You’ll not only speed up your application’s performance, but you’ll save yourself a big headache when your application encounters a large volume of usage.