Therefore SQL skills are indispensable in most of the job roles. Relational databases are one of the most popular databases, and SQL is the basis of relational databases. In this tutorial, you have learned how to use the MySQL composite index to speed up the queries.In the era where data is being generated in humongous amounts, there is a constant need to handle data in databases. Similarly, the query optimizer cannot use the name index for the lookups in the following query because either the firstName or lastName column is used for lookups. The query optimizer cannot use the name index for lookups in the following queries because only the firstName column which is not the leftmost prefix of the index is used: SELECT The following statement verifies the index usage: EXPLAIN SELECT This query is similar to the second one which both lastName and firstName columns are used for lookups. In this query, both lastName and firstName columns are used for lookups, therefore, it uses the name index.ģ) Find employees whose last name is Patterson and first name is Steve or Mary: SELECT You can verify this by adding the EXPLAIN clause to the query: EXPLAIN SELECTĢ) Find employees whose last name is Patterson and the first name is Steve: SELECT This query uses the name index because the leftmost prefix of the index, which is the lastName column, is used for lookups. The name index, therefore, is used for lookups in the following queries:ġ) Find employees whose last name is Patterson SELECT Second, the name index can be used for queries that specify values for the combination of the lastName and firstName values. The following statement creates a composite index over the lastName and firstName columns: CREATE INDEX name ON employees(lastName, firstName) įirst, the name index can be used for lookups in the queries that specify a lastName value because the lastName column is the leftmost prefix of the index. We will use the employees table in the sample database for the demonstration. For example, the following queries cannot use the composite for lookups: SELECTĬode language: SQL (Structured Query Language) ( sql ) MySQL composite index example The query optimizer cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Notice that if you have a composite index on (c1,c2,c3), you will have indexed search capabilities on one the following column combinations: (c1) Or you can add a composite index to an existing table by using the CREATE INDEX statement: CREATE INDEX index_name In this syntax, the composite index consists of three columns c2, c3, and c4. To create a composite index at the time of table creation, you use the following statement: CREATE TABLE table_name (Ĭode language: SQL (Structured Query Language) ( sql ) If you specify the columns in the right order in the index definition, a single composite index can speed up these kinds of queries on the same table. The query optimizer uses the composite indexes for queries that test all columns in the index, or queries that test the first columns, the first two columns, and so on. MySQL allows you to create a composite index that consists of up to 16 columns.Ī composite index is also known as a multiple-column index. Introduction to MySQL composite indexĪ composite index is an index on multiple columns. Summary: in this example, you will learn about the MySQL composite index and how to use it to speed up your queries.
0 Comments
Leave a Reply. |