0
0
Mitul Dabhi
answered Nov 30 '-1 00:00
there are lots of factor which will help to improve speed of Query execution and data fetch .
1. proper table structure
(A) choose right data Type for column .right data type help you to imporve speed of SQL Query many way .
-> wrong data type lead extra over head for additional functionality .
suppose if id , column which used for indexing , have varchar data type than it is not easy to do indexing . it should be integer so you can sort by id , compare exact value when needed.
(B) Assign proper valid size to column .
(C) Take less column when read/write of data happening more .
-> it is easy to fetch less column
(D) Place indexes in place to make queries efficient
-> suppose you have student and their subject table . if you place subject index at student table its very easy to search by subject for all student very easy.
(E) Use proper storage Engine
-> MySQL have differ storage Engine for differ use . following is storage engine at with its characteristic
MySQL have 8 storage engines
MEMORY - Hash based, stored in memory,
FEDERATED - Federated MySQL storage engine
MyISAM - Default engine as of MySQL 3.23
BLACKHOLE - /dev/null storage engine
MRG_MYISAM - Collection of identical MyISAM tables
CSV - CSV storage engine
ARCHIVE - Archive storage engine
InnoDB - Supports transactions, row locking and foreign keys.
if you want to use for transnational purpose you can go with InnoDB storage engine in MySQL , its easy to lock row and other transnational related job easy
if you want to use for non transnational purpose , MyISAM storage engine is good to use in MySQL.
2) OPTIMIZE the SQL Query
A) remove extra over head for fetching data.
select * from employee
suppose you running the Query for fetch only two column from 8 other column so you are fetching 6 column extra.which is extra over ahead.
try to avoid it by specifying column name which you need.
select name,email from employee
B) try to avoid joining if you do not need.
joining two table make more slower.
use count(*) without where when have situation
COUNT(*) on a single table without a WHERE is retrieved directly from the table information for MyISAM and MEMORY tables.