The way to achieve fast searches is to add an index, either when creating a table or at any time afterward. But the decision is not so simple. For example, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. Also, you must decide which columns require an index, a judgment that requires you to predict
whether you will be searching any of the data in each column. Indexes can get more complicated too, because you can combine multiple columns in one index. And even when you\u2019ve decided that, you still have the option of reducing index size by limiting the amount of each column to be indexed. If we imagine the searches that may be made on the classics table, it becomes apparent that all of the columns may need to be searched.
<\/p>\n\n\n\n
ALTER TABLE classics ADD INDEX(author(20)); The first two commands create indexes on the author and title columns, limiting each index to only the first 20 characters. For instance, when MySQL indexes the following title:<\/p>\n\n\n\n Markku Aro This is done to minimize the size of the index, and to optimize database access speed. I chose 20 because it\u2019s likely to be sufficient to ensure uniqueness for most strings in these columns. If MySQL finds two indexes with the same contents, it will have to waste time going to the table itself and checking the column that was indexed to find The way to achieve fast searches is to add an index, either when creating a table or at any time afterward. But the decision is not so simple. For example, there are different index types, such as a regular INDEX, a PRIMARY KEY, or a FULLTEXT index. Also, you must decide which columns require an … Continue reading Creating an Index<\/span>
ALTER TABLE classics ADD INDEX(title(20));
ALTER TABLE classics ADD INDEX(category(4));
ALTER TABLE classics ADD INDEX(year);
DESCRIBE classics;<\/strong><\/p>\n\n\n\n
It will actually store in the index only the first 20 characters:
Markku Aro <\/p>\n\n\n\n
out which rows really matched. With the category column, currently only the first character is required to identify a string as unique (P for POP, I for Iskelm\u00e4, and C for Classic), but I chose an index of four characters to allow for future categories that may share the first three
characters. You can also reindex this column later, when you have a more complete set of categories. And finally, I set no limit to the year column\u2019s index, because it has a clearly defined length of four characters.<\/p>\n","protected":false},"excerpt":{"rendered":"