Using Indexes in MariaDB
You can define a column that will be indexed in MariaDB when you create a new table as a clause in the CREATE TABLE statement, or on an existing table using the CREATE INDEX command.
Defining Key Columns
You have already seen that the primary key on a table can be defined by adding the keywords PRIMARY KEY after a column in the table definition. You also can add a key definition as a separate item in the CREATE TABLE statement.
A primary key is specified using the keywords PRIMARY KEY followed by the name of the column in parentheses. You can also specify a UNIQUE KEY or UNIQUE INDEX, or simply a KEY or INDEX with no further constraints. The column name is given in parentheses after the key type.
The following statement shows an alternative CREATE TABLE statement for the products table in the sample database. The table it creates is the same as if PRIMARY KEY were specified alongside the product_code column and the name column was given the UNIQUE attribute.
CREATE TABLE products ( product_code VARCHAR(10), name VARCHAR(40) NOT NULL, weight DECIMAL(6,2) NOT NULL, price DECIMAL(6,2) NOT NULL, PRIMARY KEY (product_code), UNIQUE KEY (name) )
You can use the ALTER TABLE command to add indexes to a table using this syntax in the same way you would add new columns to a table. The following example defines a new index on the customer_code column in the orders table:
MariaDB> ALTER TABLE orders -> ADD INDEX(customer_code); Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0
|
Using CREATE INDEX
The CREATE INDEX command offers another way to add an index on a database column. This command requires you to name the index as well as specify the table name and column.
The following statement creates the same index you created in the previous example on orders.customer_code. The index is named cust_code_idx.
MariaDB> CREATE INDEX cust_code_idx -> ON orders(customer_code); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0
If you execute both this statement and the previous example, you will not see an errorMariaDB will create two indexes on the same column of the same table. However, because each index name must be unique, you will generate an error if you try to execute this CREATE INDEX statement again.
Viewing Indexes on a Table
To view the keys or indexes on a table, use the SHOW KEYS or SHOW INDEXES commands. The result of both commands is the same; whether you use CREATE INDEX or define the column as a key makes no difference.
The following example shows the indexes on the orders table. The output shown assumes that you executed both of the previous two examples, so the index on customer_code was created twice.
MariaDB> SHOW INDEXES FROM orders; +---------+------------+----------------+---------------+ | Table | Non_unique | Key_name | Column_name | +---------+------------+----------------+---------------+ | orders | 0 | PRIMARY | order_id | | orders | 1 | customer_code | customer_code | | orders | 1 | cust_code_idx | customer_code | +---------+------------+----------------+---------------+ 3 rows in set (0.00 sec)
The output shown here has been modified because of space constraints in this book. The actual output you will see contains a few other columns.
The first row contains the primary key for the table. The other rows are the additional indexes you created. The one created through the ALTER TABLE statement was given the name customer_code, shown in the Key_name column. The Non_unique column contains 0 for the primary key and 1 for the other keys, which were not created with UNIQUE.
|
Dropping Indexes
To drop an index from a table, you need to know its name, which you have to check using the SHOW INDEXES command unless you specified the name yourself.
The following command drops the cust_code_idx index using the DROP INDEX command, leaving all other indexes and all the data in the table intact:
MariaDB> DROP INDEX cust_code_idx -> ON orders; Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0
Alternatively, you could use the ALTER TABLE command with DROP INDEX. The following statement would perform the same operation as the previous example:
ALTER TABLE orders DROP INDEX cust_code_idx;
|
Partial Indexes
When creating an index on a string value, you can tell MariaDB to use only part of the value rather than the whole string in the index. The following statement creates an index on the name column in the customers table, but using only the first five characters of the name:
ALTER TABLE customers ADD INDEX (name(5));
Or, you can use the CREATE INDEX statement:
CREATE INDEX cust_name_idx ON customers(name(5));
A partial index can be useful when you index columns that can potentially contain large values. The index will have a smaller storage requirement while still assisting queries in finding values from the column quickly.
MariaDB compares the specified number of characters in the key to locate matching rows first. If more than one row matches the partial key, it checks those rows to find an exact match.
Compound Indexes
You can specify two or more columns when creating an index, and MariaDB will create an index that uses the values from both columns in the order given. This is known as a compound index.
The following statement creates a compound index on the weight and price columns in the products table:
ALTER TABLE products ADD INDEX (weight, price);
Or, you can use the CREATE INDEX statement:
CREATE INDEX products_weight_price_idx ON products(weight, price);
Usually you create a compound index on two columns that are frequently used in a single query or are both used in a table join. MariaDB can use only one index on each table in a query, so two separate indexes on the same two columns cannot both be used.
The index created by the previous example could speed up a query on the products table that had a filter on both weight and price. Additionally, because weight is the first column specified, this index could be used effectively on queries that filter just on weight. However, it would be ineffective on queries that filter on price only.
|
The Full-Text Index
The full-text index is a special type of index MariaDB implements that is very efficient for free text searching. You can create a full-text index on a TEXT or VARCHAR type column and then perform text matching using the MATCH() function.
You specify a full-text index using the FULLTEXT keyword in the table definition or on an existing table using the ALTER TABLE statement. The following statement adds a full-text index to the customer_contacts table on the first_name and last_name columns:
MariaDB> ALTER TABLE customers -> ADD FULLTEXT(name); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0
The MATCH() function works only with columns that have a full-text index. You specify the column to search in parenthesesif you have more than one full-text index, you can specify a list of columnsfollowed by the keyword AGAINST and a list of values to search for.
The following example searches the customers table for a name that matches the string Corporation:
MariaDB> SELECT * FROM customers
-> WHERE MATCH(name) AGAINST ('Corporation');
+---------------+---------------------+
| customer_code | name |
+---------------+---------------------+
| SCICORP | Science Corporation |
| CORPLTD | Corporation Limited |
+---------------+---------------------+
2 rows in set (0.00 sec)
|
Foreign Key Constraints with InnoDB
The InnoDB table handler includes support for foreign key constraints, which are used to enforce the rule that a column value may contain only a value that is present in a given column in another table.
For example, a foreign key on the customer_code column in the orders table that references customers.customer_code would ensure that you can enter rows into orders only for customers that are already in the database.
The sample tables do not have their foreign keys enforced at the database level, to ensure that they work on systems on which InnoDB is not available. The following example shows how the CREATE TABLE statement for orders would be amended to include this constraint:
CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, customer_code VARCHAR(10) NOT NULL, order_date DATE NOT NULL, FOREIGN KEY (customer_code) REFERENCES customers(customer_code) ) ENGINE=InnoDB;
|
The following example attempts to insert a new row into the orders table and shows the error message that is displayed when the foreign key is violated:
MariaDB> INSERT INTO orders (customer_code, order_date)
-> VALUES ('NODDY', NOW());
ERROR 1452 (23000): Cannot add or update a child row: a Foreign key constraint fails (`mysql10/orders2`, CONSTRAINT
`orders2_ibfk_1` FOREIGN KEY (`customer_code`) REFERENCES
`customers`
(`customer_code`))
Consider what might happen if you delete a record from the customers table for which there are corresponding rows in orders. The records in the orders table would then violate the foreign key constraint.
To avoid this, you can specify an action to take when values referenced by a foreign key are changed or removed using the ON UPDATE or ON, DELETE keywords in a FOREIGN KEY clause. The possible values are as follows:
-
CASCADEWhen a referenced value in the parent table is updated, corresponding rows in the child table have the same update performed on their foreign key columns. If the value in the parent table is deleted, rows in the child table are automatically deleted when a foreign key is deleted from the parent table.
-
SET NULLWhen a referenced value in the parent table is updated or deleted, corresponding rows in the child table have their foreign key columns set to NULL. This action is not possible if the foreign key column has the NOT NULL constraint.
-
NO ACTIONRecords will never be modified or deleted in the child table. Instead, if an UPDATE or DELETE on the parent table would change or remove a referenced value, the action is prohibited. This is the default action if no ON UPDATE or ON DELETE action is given.
The following statement can be used to add the foreign key described in the previous example to the customers table, using CASCADE for updates and NO ACTION to prevent referenced columns from being deleted from the parent table:
ALTER TABLE orders ADD FOREIGN KEY(customer_code) REFERENCES customers(customer_code) ON UPDATE CASCADE ON DELETE NO ACTION;
Then, if you update a record in the customers table, corresponding rows in orders will be updated with the same customer code.
MariaDB> UPDATE customers -> SET customer_code = 'NEWCODE' -> WHERE customer_code = 'MUSGRP'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB> SELECT * FROM orders -> WHERE customer_code = 'NEWCODE'; +----------+---------------+------------+ | order_id | customer_code | order_date | +----------+---------------+------------+ | 6 | NEWCODE | 2006-02-01 | | 7 | NEWCODE | 2006-02-02 | | 9 | NEWCODE | 2006-04-01 | | 10 | NEWCODE | 2006-04-01 | +----------+---------------+------------+ 4 rows in set (0.00 sec)
However, attempting to remove a referenced row from the customers table is prohibited and gives an error, as shown in the following example:
MariaDB> DELETE FROM customers -> WHERE customer_code = 'SCICORP'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysql10/orders`, CONSTRAINT `orders_ibfk_1`FOREIGN KEY (`customer_code`) REFERENCES `customers` (`customer_code`) ON DELETE NO ACTION ON UPDATE CASCADE)
|
  |