A number of points must be examined if we want to improve our structure's efficiency in terms of access speed or disk space used.
Adding indexes on columns that are used in a WHERE
clause is a common way of speeding up the queries. Let's say that we intend to find all vehicles for a specific brand. The vehicle
table has a brand_id
column and we want to create an index on this column. In this case, the index won't be unique because each brand is represented by many vehicles.
Using phpMyAdmin, there are two ways to create an index. First, if the index applies to a single column, we can open the Structure page for a table and click the index (flash) icon on the same line as the brand_id
column:
This generates the following statement:
ALTER TABLE `vehicle` ADD INDEX(`brand_id`)
We could also create an index on a composite key, for example model_id
plus year
. For this, we enter the number of columns for our index (two) on the Structure page and hit Go.
Next, on the index management...