MySQL offers many types of indexes, usually categorized according to their data structure. However, note that a table can only contain a single PRIMARY KEY, and the key must contain UNIQUE values and no NULL values: CREATE TABLE Customer ( id int NOT NULL, last_name varchar( 30) NOT NULL, first_name varchar( 30) NOT NULL, email varchar( 50) NOT NULL, CONSTRAINT PK_Customer PRIMARY KEY ( id,last_name)) Code language: SQL (Structured Query Language) ( sql )įor existing tables, you can also add indexes as follows: ALTER TABLE Customer ADD INDEX (email) ĪLTER TABLE Customer ADD PRIMARY KEY ( id) Code language: SQL (Structured Query Language) ( sql )ĭeleting existing indexes is simple, just use the DROP INDEX statement: ALTER TABLE Customer DROP INDEX (email) Code language: SQL (Structured Query Language) ( sql ) Types of Indexes You can also use a PRIMARY KEY statement instead of an INDEX statement. ) Code language: SQL (Structured Query Language) ( sql ) You can also set up indexes when creating a new table using the INDEX statement: CREATE TABLE Customer ( Indexing basicsĬreating an index in MySQL is done using the CREATE INDEX command, and has the following syntax: CREATE INDEX index_name ON table_name ( column1, column2.) Code language: SQL (Structured Query Language) ( sql ) It’s basically the most important thing database engineers consider when optimizing databases for speed. Indexing is one of the most powerful features of a database. The MySQL Join Optimizer also uses indexes to speed up queries that involve joins. Additionally, indexes enable data to be better organized on disk. This enables the MySQL server to skip searching long rows of a table to find that piece of data thus boosting query speed. Indexes are made up of keys from one or more columns in a table and they contain pointers that tell MySQL where a particular row of data is stored in the database. Have you ever tried finding a chapter in a huge book, and you head over to the “Table of Contents” or Index page and seek the page number for the chapter? That’s a similar way DBMS use indexes to speed up data retrieval.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |