As things stand, the table classics works and can be searched without problem by MySQL—until it grows to more than a couple hundred rows, that is. At that point, database accesses will get slower and slower with every new row added, because MySQL has to search through every row whenever a query is issued. This is like searching through every book in a library whenever you need to look something up.
Of course, you don’t have to search libraries that way, because they have either a card index system or, most likely, a database of their own. And the same goes for MySQL, because at the expense of a slight overhead in memory and disk space, you can create a “card index” for a table that MySQL will use to conduct lightning-fast searches.
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
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 that column. Indexes can also get complicated, because you can
combine multiple columns in one index. And even when you’ve 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. ...