CODEWITHSUNDEEP
mysqlsql
Flasz
Flasz

Reputation: 185

How to optimize MySQL query for a large database

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

Upvotes: 0

Views: 1082

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Add an index on (projectname, time):

ALTER TABLE log
  ADD INDEX projectname_time_IX            -- choose a name for the index
    (projectname, time) ;

And then use the original column for the ORDER BY

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

or this variation - to make sure that the index is used effectively:

  ( SELECT *, UNIX_TIMESTAMP(time) AS unix_time 
    FROM log 
    WHERE projectname = 'test'
    ORDER BY time DESC 
    LIMIT 20 
  )
  UNION ALL 
  ( SELECT *, UNIX_TIMESTAMP(time) AS unix_time 
    FROM log 
    WHERE projectname IS NULL
    ORDER BY time DESC 
    LIMIT 20 
  ) 
  ORDER BY time DESC
  LIMIT 20 ;

Upvotes: 1

Related Questions