If you\u2019ve been following SimpleTalk for a while, you will be aware that I\u2019ve recently blogged about MySQL indexes. I\u2019ve already told you about the nuances of indexes in MySQL<\/a>, vanilla B-Tree indexes<\/a>, composite B-Tree indexes<\/a>, and other kinds of indexes too.<\/p>\n\n\n\n This blog will walk you through how to choose the perfect index for your specific use case. It will also advise you on the things you should consider to devise the perfect index design in MySQL.<\/p>\n\n\n\n One thing you should take away from those blogs is that there are two general types of indexes in MySQL that are often split into multiple different sub-types. We have B-tree indexes, and R-tree, or spatial, indexes.<\/p>\n\n\n\n Most indexes will be related to the B-tree index type, and those that aren\u2019t will be considered R-tree indexes. Covering, composite, clustered, descending, unique, hash, full-text, and primary key indexes will be B-tree indexes, and spatial indexes will be R-tree indexes.<\/p>\n\n\n\n A B-tree index is a balanced tree index. A balanced tree index is a data structure that sorts data and facilitates searching for data that\u2019s stored in a sorted order. It thus enables your database to find rows quicker.<\/p>\n\n\n\n An R-tree index is an index that\u2019s used to find data falling within a general range, and then perform operations on that data. R-tree indexes are used to facilitate spatial searches \u2013 that is, searches through geographic data sets.<\/p>\n\n\n\n If you consider using indexes, you\u2019re most likely considering them to facilitate an end goal for your specific use case. That end goal is most likely related to one or more of the following points:<\/p>\n\n\n Indexes help these use cases because they can eliminate rows from consideration when a WHERE clause is in use.<\/p>\n\n\n\n Most of your use cases will necessitate vanilla B-tree indexes unless your use case deals with geographical data. B-tree indexes can be applied when creating a table. To do that, add an INDEX clause once you define all of the necessary columns:<\/p>\n\n\n\n Or when modifying data using the Adding an index is easy, but choosing the specific index and designing it as necessary is a little harder.<\/p>\n\n\n\n To choose an index for your use case, consider the following:<\/p>\n\n\n Rules involving indexes are simple and they\u2019re not rocket science, however, one must be aware of the fact that each type of indexing comes with its unique propositions and problems. Most of those problems are already covered in my series on indexes in MySQL<\/a>, so I won\u2019t get into them here, so if you\u2019re curious, give these blogs a read!<\/p>\n\n\n\n *A \u201cvanilla\u201d B-tree index is a B-tree index that is not a covering, composite, clustered, descending, unique, hash, full-text, or primary key index.<\/em><\/p>\n\n\n\n **Wildcards are not exclusive to full-text searches and they can also be used when no indexes are in use or when other types of indexes are being used<\/em>. Keep in mind that you cannot use them using the \u201c*\u201d symbol \u2013 we have to choose the percentage sign (\u201c%\u201d) instead.<\/em><\/p>\n\n\n\n Once you\u2019ve chosen the type of index you are going to employ to help your use case, it\u2019s time to devise the perfect index design.<\/p>\n\n\n\n The way your \u201cperfect\u201d index design will look like will depend on a couple of factors, the main ones being:<\/p>\n\n\n These two things are closely linked \u2013 choose the necessary index based on your query structure, and your query structure will be dictated by your use case.<\/p>\n\n\n\n Here are a couple of examples:<\/p>\n\n\n\n For many \u201cvanilla\u201d use cases you may choose a vanilla B-tree index as shown in some of the examples below. Some use cases may necessitate you using a covering index (consider a covering index when you search through a lot of data<\/em>.)<\/p>\n\n\n\n Some use cases may necessitate a partial index to benefit from a B-tree index and save disk space at the same time. For a partial index to be effective, define the index on \u201cactive\u201d (i.e. used by the user) amount of characters in the column. To choose a proper character count for the partial index, consider your use case and define the partial index on the average number of characters that are searched through. For example, if your use case involves searching for email addresses, defining a partial index on the first 20 characters should help.<\/p>\n\n\n\n Choosing a proper index and designing it properly is vital for your use case. My indexing series here on Simple-Talk<\/a> should give you a good enough insight into the index types available to choose from in MySQL, while this blog should give you enough examples to make adequate choices quickly.<\/p>\n\n\n\nAbout Indexes in MySQL<\/h2>\n\n\n\n
Indexing for Your Use Case<\/h2>\n\n\n\n
\n
CREATE TABLE `demo_table` (<\/code>`col` VARCHAR(115) NOT NULL,<\/code>`col2` INT(5) NOT NULL,<\/code>INDEX `idx_name`(`col`)<\/strong><\/code>); <\/code> <\/p>\n\n\n\nCREATE INDEX<\/code> or ALTER<\/code> clauses:<\/p>\n\n\n\nCREATE INDEX `btree_idx` ON `demo_table`(`column`);<\/code><\/p>\n\n\n\nALTER TABLE `demo_table` ADD INDEX `btree_idx`(`column`);<\/code><\/pre>\n\n\n\nWhat Index to Choose for Your Use Case? Tips & Tricks<\/h2>\n\n\n\n
\n
WHERE<\/code><\/em> clause involves multiple columns, your query includes <\/em>OR<\/code><\/em> clauses, or you want to make the query optimizer able to directly access the table if the column is not in the index.<\/em><\/li>\n\n\n\nORDER BY<\/code> clause to sort the results of a query in descending order, etc.)<\/li>\n\n\n\nRules Involving Indexes<\/h3>\n\n\n\n
How to Devise the Perfect Index Design? Examples, Tips & Tricks<\/h2>\n\n\n\n
\n
Examples<\/h3>\n\n\n\n
ID<\/strong><\/td> Query or Use Case<\/strong><\/td> Index to Choose<\/strong><\/td> Definition of the Index<\/strong><\/td><\/tr> 1<\/td> SELECT * FROM `demo` WHERE `col` = \u2018Value\u2019;<\/code><\/td>Vanilla B-tree index<\/td> CREATE INDEX `idx` ON `demo`(`col`);<\/code><\/td><\/tr>2<\/td> SELECT * FROM `demo` WHERE `col` = \u2018Value\u2019 AND `c2` = \u2018Value 2\u2019;<\/code><\/td>Vanilla B-tree index (covering index)<\/td> CREATE INDEX `idx` ON `demo`(`col`,`c2`);<\/code>
Alternatively, index #1.<\/td><\/tr>3<\/td> SELECT * FROM `demo` WHERE MATCH(col) AGAINST(\u201cValue\u201d);<\/code><\/td>Full-text index<\/td> CREATE FULLTEXT INDEX `ftx` ON `demo`(`col`);<\/code><\/td><\/tr>4<\/td> SELECT data FROM `demo` WHERE id = 1;<\/code><\/td>Clustered index<\/td> ALTER TABLE `demo` ADD PRIMARY KEY(`id`);<\/code><\/td><\/tr>5<\/td> A lot of rows, adding a B-tree doesn\u2019t make sense, but data has to be indexed for some<\/em> efficiency.<\/td> Partial index<\/td> CREATE INDEX `partial_idx` ON `demo`(`col`(10));<\/code><\/td><\/tr>6<\/td> SELECT id,username FROM `demo` WHERE regdate LIKE \u20182016%\u2019 ORDER BY id DESC<\/strong>;<\/code><\/code><\/td>Descending index<\/td> CREATE INDEX `desc_idx` ON `demo`(`regdate`, `id` DESC); or CREATE INDEX `desc_idx` ON `demo`(`regdate` DESC);<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nSummary<\/h2>\n\n\n\n