Optimization Techniques

DB INDEXING

Indexing


 A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. 
WIKIPEDIA

PROS and CONS

PROS
  • makes search queries execute quicker
  • Avoid data row duplication (unique/primary indexes only)
  • can be useful in full text searches 

CONS
  • Will slow down insert/update queries .
  • Additional data on the table .(negligible)

When to Use Indexes

You need use indexes when you need to fasten your searches (data retrieval) But remember , You always have to be careful before creating indexes because , over indexing will hurt the performance in a bad way . 

Note : Before using indexes
  • Check whether your application has frequent writes(add/edit) to the DB . If you have frequent create/updates you might need to go for an alternative method .

Analyzing Queries

  • You can use EXPLAIN keyword in front of the SELECT query to analyse the query
explain SELECT student_id, name FROM `student` WHERE name like 'a%'  
explain SELECT student_id, name FROM `student` order by name

EXPLAIN - Data

  • table -  gives the table name
  • possible_keys -  all the possible keys that MYSQL could use
  • key -  gives which key was chosen
  • rows -  gives no of rows searched 
  • type -   type [ ALL , REF , EQ_REF......... ] .
  • Extra -  gives how the search is performed


Cardinality

  • gives you the no of unique keys available for the given index


Cardinality is equal to the no of rows in the table if it is an unique index

Our Goal 


  • Reduce the execution Time of the query
    • Try to reduce no of rows searched
    • Try to avoid USING FILE SORT in EXTRA column

Note : FILE SORT does a full search on your table Therefore needs to be avoided . However if the table is small this will not cause much trouble

Create INDEX Explained

CREATE [ UNIQUE | FULLTEXT |SPATIAL ] INDEX index_name
USING [ BTREE | HASH | RTREE ] 
ON table_name (column_name [(length)] [ ASC | DESC ])

  • FULL TEXT  and  SPATIAL types are for MYISAM Engines
  • Both MYISAM and INNODB use only BTREE
  • Indexed Columns can be NULL unless its a primary key.(if index is of type SPATIAL columns cannot be null )

hOW TO CREATE AN iNDEX

 (mysql)

CREATE INDEX index_name ON TableName (FieldName); 

Alternatives

ALTER TABLE TableName ADD INDEX index_name (FieldName);

note : you can also add a specify a length on the column

ALTER TABLE TableName ADD INDEX index_name ( FieldName (length) ) ;

WHEN to use Indexes 

EXAMPLES

consider these 2 tables

CREATE TABLE `student` (
  `student_id` mediumint unsigned NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `age` mediumint default NULL,
  `email` varchar(255) default NULL,
  `tp_no` varchar(100) default NULL,
  `dept_id` mediumint default NULL,
  PRIMARY KEY (`student_id`)
) AUTO_INCREMENT=1; 
CREATE TABLE `department` (
  `dept_id` mediumint unsigned NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY (`dept_id`)
) AUTO_INCREMENT=1;

SCENARIO

now consider this scenario where you have this frequent call for a query like this for the previous table

SELECT student_id, name FROM `student` WHERE name like 'a%' 
  • this certain query will be searching all the records from the student table  which is going to cost high and if you have many records its gonna  be a disaster
  • If you add an index on this the queries will get much faster 
create index index_student_name on student(name)

DEMO 1

before indexing


after indexing 

VOILÀ ..... we have reduced the number of rows searched by indexing

Demo 2 -order

explain SELECT student_id, name FROM `student` order by name
Before Indexing


After Indexing 

DEMO -JOINS

EXPLAIN SELECT s.student_id, s.name, d.name
FROM student s
LEFT JOIN department d ON s.dept_id = d.dept_id
WHERE d.name LIKE 'M%'
This is not a good example since i have very less no of rows in my department table and obviously there won't be much of rows in department table . But I just wanted to show how EXPLAIN works in a JOIN
it will show 1 row for each table  (I think you can figure out the rest )

after indexing

Things YOU SHOULD DO

  • create your own tables and insert data (GO to generatedata.com it can help to build your SQL scripts with Dummy Data )
  • create select queries with EXPLAIN and check with and without indexing
  • Read MYSQL Documentation for more Info

SQL Optimization Techniques

By malitha gamage

Made with Slides.com

SQL Optimization Techniques

DB Indexing - MySQL , Query Optimization

  • 1,344

More from malitha gamage