# Mysql Performance Tips
# Optimizing Storage Layout for InnoDB Tables
When using COMPACT row format (the default InnoDB format) and variable-length character sets, such as utf8 or sjis, CHAR(N) columns occupy a variable amount of space, but still at least N bytes.
OPTIMIZE TABLE copies the data part of the table and rebuilds the indexes. The benefits come from improved packing of data within indexes, and reduced fragmentation within the tablespaces and on disk. The benefits vary depending on the data in each table. You may find that there are significant gains for some and not for others, or that the gains decrease over time until you next optimize the table. This operation can be slow if the table is large or if the indexes being rebuilt do not fit into the buffer pool. The first run after adding a lot of data to a table is often much slower than later runs.
# Building a composite index
In many situations, a composite index performs better than an index with a single column. To build an optimal composite index, populate it with columns in this order.
=
column(s) from theWHERE
clause first. (eg,INDEX(a,b,...)
forWHERE a=12 AND b='xyz' ...
)IN
column(s); the optimizer may be able to leapfrog through the index.- One "range" (eg
x BETWEEN 3 AND 9
,name LIKE 'J%'
) It won't use anything past the first range column. - All the columns in
GROUP BY
, in order - All the columns in
ORDER BY
, in order. Works only if all areASC
or all areDESC
or you are using 8.0.
Notes and exceptions:
- Don't duplicate any columns.
- Skip over any cases that don't apply.
- If you don't use all the columns of
WHERE
, there is no need to go on toGROUP BY
, etc. - There are cases where it is useful to index only the
ORDER BY
column(s), ignoringWHERE
. - Don't "hide" a column in a function (eg
DATE(x) = ...
cannot usex
in the index.) - 'Prefix' indexing (eg,
text_col(99)
) is unlikely to be helpful; may hurt.
More details and tips (opens new window) .
# Select Statement Optimization
Below are some tips to remember while we are writing a select query in MySQL that can help us and reduce our query time:-
Whenever we use where in a large table we should make sure the column in where clause are index or not. Ex:- Select * from employee where user_id > 2000. user_id if indexed then will speed up the evaluation of the query atlot. Indexes are also very important during joins and foreign keys.
When you need the smaller section of content rather then fetching whole data from table, try to use limit. Rather then writing Ex:- Select * from employee. If you need just first 20 employee from lakhs then just use limit Ex:- Select * from employee LIMIT 20.
You can also optimize your query by providing the column name which you want in resultset. Rather then writing Ex:- Select * from employee. Just mention column name from which you need data if you table has lots of column and you want to have data for few of them. Ex:- Select id, name from employee.
Index column if you are using to verify for NULL in where clause. If you have some statement as SELECT * FROM tbl_name WHERE key_col IS NULL; then if key_col is indexed then query will be evaluated faster.