PHP Tutorials
- What is MySqli
- mysql query
- mysql query example
- InnoDB
- mysql column Type
- CRUD Example
- Connection Using Function
- mysql keys
- SELECT
- WHERE
- UPDATE
- Count no of Rows
- ALIAS
- AND, AND & OR
- BETWEEN
- COMPARISON OPERATOR
- DELETE
- DELETE LIMIT
- DISTINCT
- EXISTS
- FROM
- GROUP BY
- HAVING
- IN
- INTERSECT
- IS NULL & IS NOT NULL
- LIKE
- NOT
- ORDER BY
- SELECT LIMIT
- SUBQUERY
- TRUNCATE
- UNION && UNION ALL
- Concat & Group_Concat
- mysql Function
- Mysql Insert Id
- MySql Aggregate Function
- Mysql Join
- JOIN in MySql
- Trigger
- Procedure
- Transaction
- views
- Index
- SQL Injection
- Normalization
- Query Bind
- Interview Questions
Important Link
what is MySql Index?
An index is a schema object. It is used by the server to speed up data retrieval. It can reduce disk I/O operation by using a rapid path access method to locate data quickly. An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements. Indexes can be created or dropped with no effect on the data.
an index is a pointer to data in a table
An INDEX is created on columns of a table. One table may contain one or more INDEX tables.
CREATE INDEX
File Name :
Syntax :-
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX index ON TABLE column;
create INDEX index ON users pk_id;
Here index is the name given to that index and TABLE is the name of the table on which that index is created and column is the name of that column for which it is applied.
Unique Indexes
You can Creates a unique index on a table. in unique index Duplicate values are not allowed:
File Name :
CREATE UNIQUE INDEX index ON TABLE_name column_name;
When should indexes be created:
When should indexes be avoided:
File Name :
Indexes should not be used on small tables.
Tables that have frequent, large batch updates or insert operations.
The columns are not often used as a condition in the query
The column is updated frequently
Indexes should not be used on columns that contain a high number of NULL values.
Columns that are frequently manipulated should not be indexed.
Removing an Index:
To remove an index from the data dictionary by using the DROP INDEX command.
File Name :
DROP INDEX index;
Altering an Index:
File Name :
ALTER INDEX IndexName ON Table_Name Column_name;
Confirming Indexes :
You can check the different indexes present in a particular table
File Name :
select * from USER_INDEXES;
How to speed up your MySQL queries 500 times
File Name :
SELECT picture.id, picture.title FROM picture LEFT JOIN album ON picture.album_id = album.id
WHERE album.user_id = 1;
row for both table that was involved in the query:
File Name :
query_image_join.png
The important pieces here are the table name, the key used, and the number of rows scanned during the execution of the query.
File Name :
It scans 2,000,000 pictures, then, for each picture, it scans 20,000 albums. This means that it actually scans 40 billion rows for the album table. However, you can make this process much more efficient.
Indexes
You can increase performance by using indexes. you can add an index on picture.album_id
File Name :
ALTER TABLE picture ADD INDEX(album_id);
Now if you run the query, the process no longer involves scanning the entire list of pictures. First, all the albums are scanned to find the ones that belong to the user. After that, the pictures are quickly located using the indexed album_id column. This reduces the number of rows scanned to 200,000. The query is also about 317 times faster than the original.
File Name :
You can make sure that both tables use a key by adding the following index:
File Name :
ALTER TABLE album ADD INDEX(user_id);
File Name :
This time, the album table is not scanned in its entirety, but the right albums are quickly pinpointed using the user_id key. When these 100 albums are scanned, associated pictures are pinpointed using the album_id key. Each table uses a key for an optimal performance, making the query 380 times faster than the original.
File Name :
File Name :