CODEWITHSUNDEEP
mysql
FlyFly Wing
FlyFly Wing

Reputation: 109

MySQL Check historical queries being run

Is there a table/place that stores all the historical queries being run on MySQL?

I want to do an analysis of the historical queries in order to determine what INDEX to create in each table.

Upvotes: 2

Views: 1274

Answers (1)

Ben
Ben

Reputation: 2255

You can do that by creating slow_log or general_log tables.

MySQL Server provides a way to show the general query log and the slow query log, if those logs are enabled.

First, check if you already have the two tables slow_log and general_log existing in the MySQL database.

If you don't have them already - then you have to create them. Make sure that you are creating them in the MySQL database.

Create general_log table:

CREATE TABLE `general_log` (
    `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `user_host` mediumtext NOT NULL,
    `thread_id` bigint (21) unsigned NOT NULL,
    `server_id` int(10) unsigned NOT NULL,
    `command_type` varchar(64) NOT NULL,
    `argument` mediumtext NOT NULL) ENGINE = CSV DEFAULT CHARSET = utf8 COMMENT = 'General log'

The general query log is a general record of what mysqld is doing.

There you will find information such as:

  • when clients connect or disconnect
  • each SQL statement received from clients

For slow_log table:

CREATE TABLE `slow_log` (
    `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `user_host` mediumtext NOT NULL,
    `query_time` time NOT NULL,
    `lock_time` time NOT NULL,
    `rows_sent` int(11) NOT NULL,
    `rows_examined` int(11) NOT NULL,
    `db` varchar(512) NOT NULL,
    `last_insert_id` int(11) NOT NULL,
    `insert_id` int(11) NOT NULL,
    `server_id` int(10) unsigned NOT NULL,
    `sql_text` mediumtext NOT NULL,
    `thread_id` bigint (21) unsigned NOT NULL) ENGINE = CSV DEFAULT CHARSET = utf8 COMMENT = 'Slow log'

The slow query log consists of SQL statements that take more than long_query_time seconds to execute and require at leastmin_examined_row_limit rows to be examined.

The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization (indexation in your case).

Then you need to enable it (if you don't already have it enabled):

SET global general_log = 1;
SET global log_output = 'table';

Now you can view the log by running this query:

SELECT * FROM   mysql.general_log;

If you want to disable query logging on the database, run this query

SET global general_log = 0;

Please note that having this turned on comes with some caveats, such as consuming disk space and similar performance considerations, so you can turn it ON and OFF by need, and not keep it always ON.

Read more about these here:

Upvotes: 1

Related Questions