Skip to main content

Category: MySql

All About Mysql Index.

atiqul Leave a comment

How to create index in MySql table.

CREATE INDEX index_name ON table_name (column_list)

CREATE INDEX jobTitle ON employees(jobTitle);

How to remove Index form a table.

DROP INDEX index_name ON table_name

DROP INDEX name ON leads;

Show index from a table.

SHOW INDEXES FROM table;

SHOW INDEXES FROM employees;

Before query optimization, we usually use EXPLAIN keyword before the query string. This EXPLAIN keyword gives us all the necessary information about that query. Among all the information Access type(type) very important info which is needed to know all the developers. So let see what is Access type.

Access type tells us, how your query accesses all the data from the dataset when you will execute the query. There are 6 types of access type values you will find. when you will put EXPLAIN keyword Before your Query string.

CONST/EQ_REF
Both work the same way so that I treat both the same types. The database is performing a B-Tree traversal to find a single value. Basically is a binary search. It’s only applicable when you have a unique data set in your result. another way you can say you get at most 1 row in your result. Two way you can do it
1. Primary key in your column.
2. You have a unique constraint on that column.
So lastly when you found this value in access type just skip no need to optimize. Because it’s not going to faster than this. it’s super fast.

REF/RANGE
They are known as index range scans. Perform a B-Tree traversal to find the starting point. after that, its scans value from that point on. It actually limits the total number of the row the database has to inspect to perform your query. It’s a good thing.

INDEX
Its also known as a Full index Scan. Index scan still using the index but its not using the limit. Its scan from at the first row and to the last row. Their is no filtering in to the index you just scan all the rows.

ALL
And lastly, All is also known as a Full Table Scan. You just load all rows into the memory not using any index. It’s a very bad access type. we should not expect this type.

Generate the table size from a Database.

atiqul Leave a comment 
SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "blog"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

If you don’t care about all tables in the database and only want the size of a particular table, you can simply add AND TABLE_NAME = "your_table_name" to the WHERE clause. Here we only want information about the book table:

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE TABLE_SCHEMA = "blog"
  AND
    TABLE_NAME = "posts"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

MySql Slow query Log.

atiqul Leave a comment

If you want to increase your application performance you need to find out where your system is going to slow. For MySQL-based applications, you can easily find out which query is slow just by enabling the slow query log.

mysql -u root -p

SET GLOBAL slow_query_log = 'ON';

By default, when the slow query log is enabled, it logs any query that takes longer than 10 seconds to run. But you can change this interval time by type the following command, replacing X with the time in seconds:

mysql -u root -p

> SET GLOBAL long_query_time = X;

Initially location is: /var/lib/mysql/hostname-slow.log.
But you can change the query log location:

mysql -u root -p

> SET GLOBAL slow_query_log_file = '/path/filename';

We can also indicate to log queries which not using indexes. which is very helpful.

mysql -u root -p

> SET GLOBAL log_queries_not_using_indexes = 1;

To find out the current slow query log location.

mysql -u root -p

> show global variables like 'slow%log%';

The parameter changes are lost after MySQL reboots because the parameters are set dynamically, so in order to make the changes permanent, we need to set up the parameters in the MySQL configuration file (/etc/mysql/my.cnf)

[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 5
log-queries-not-using-indexes = 1

We can also check the variable/parameters values as 


> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
> SHOW GLOBAL VARIABLES LIKE 'long_query_time';