MySQL Check historical queries being run

Question

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.

Good idea, but I don't think they are stored anywhere. Typically monitoring tools do this, though. — The Impaler, Feb 04 '22 at 13:19

score 1 · Answer 1 · edited Feb 08 '22 at 13:01

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.

MySQL Check historical queries being run

1 Answers1