[Solved] How to optimize MySQL query for a large database

EverSQL Database Performance Knowledge Base

How to optimize MySQL query for a large database

Database type:

I've noticed a serious problem recently, when my database increased to over 620000 records. Following query: 

SELECT *,UNIX_TIMESTAMP(`time`) AS `time` FROM `log` WHERE (`projectname`="test" OR `projectname` IS NULL)  ORDER BY `time` DESC LIMIT 0, 20

has an execution time about 2,5s on a local database. I was wondering how can I speed it up?

The EXPLAIN commands produces following output:

ID: 1
select type: SIMPLE
TABLE: log
type: ref_or_null
possible_keys: projectname
key: projectname
key_len: 387
ref: const
rows: 310661
Extra: Using where; using filesort

I've got indexes set on projectname, time columns.

Any help?

EDIT: Thanks to ypercube response, I was able to decrease query execution time. But when I only add another condition to WHERE clause (AND severity="changes") it lasts 2s again. Is it a good solution to include all of the possible "WHERE" columns to my merged-index? 

ID: 1 
select type: SIMPLE 
TABLE: log 
type: ref_or_null 
possible_keys: projectname 
key: projectname 
key_len: 419 
ref: const, const 
rows: 315554 
Extra: Using where; using filesort

Table structure:

   CREATE TABLE `log` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `projectname` VARCHAR(128) DEFAULT NULL,
  `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `master` VARCHAR(128) NOT NULL,
  `itemName` VARCHAR(128) NOT NULL,
  `severity` VARCHAR(10) NOT NULL DEFAULT 'info',
  `message` VARCHAR(255) NOT NULL,
  `more` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `projectname` (`severity`,`projectname`,`time`)
) ENGINE=INNODB AUTO_INCREMENT=621691 DEFAULT CHARSET=utf8

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid OFFSET In LIMIT Clause (query line: 12): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
  2. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  3. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `log` ADD INDEX `log_idx_projectname` (`projectname`);
The optimized query:
SELECT
        *,
        UNIX_TIMESTAMP(`time`) AS `time` 
    FROM
        `log` 
    WHERE
        (
            `log`.`projectname` = 'test' 
            OR `log`.`projectname` IS NULL
        ) 
    ORDER BY
        `time` DESC LIMIT 0,
        20

Related Articles



* original question posted on StackOverflow here.