As things stand, the table
classics works, and MySQL won’t have any problem
searching it—until it grows to more than a couple of 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—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
afterwards. But the decision is not so simple. For example, there are
different index types, such as a regular
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 those
columns. Indexes can also get complicated, because you can combine
multiple columns in one index. And even when you’ve gotten to grips with
all of 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 ...