Mysql execution time is too long

Question

I have 2 millions record in my table. when i execute a query that has 3 where condition it takes too long to response.

query :

SELECT COUNT(*) 
  FROM `table` 
 WHERE DATE(`created_at`) = '2017-08-11' 
   AND `type` in (4,13,15) 
   AND `status` = 1

execution time : 3 seconds

how can i reduce the execution time of this query or any query like this?

Edit

Table schema :

CREATE TABLE `transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL,
  `status` int(11) NOT NULL DEFAULT '2',
  `created_at` timestamp NULL DEFAULT NULL,
  ...
  PRIMARY KEY (`id`),
  KEY `query` (`created_at`,`type`,`status`),
  KEY `query2` (`status`,`type`)

I don't know my indexing is true or not

how does your table look like? I think you're missing a few `index`es — Fotis, Aug 11 '17 at 09:43

score 1 · Answer 1 · answered Aug 11 '17 at 09:49

In order to make this query run faster, you need to add indexes to your table.

First of all run the same query by adding EXPLAIN before your SELECT. This will give you a nice overview of how many rows were examined, what's the key cardinality etc.

If this is a standard query you're using, I suggest you add an index for all 3 columns. If you plan to query the columns separately, you can add an index to each column you need to query, but don't overdo it as the table will become slower.

If you run EXPLAIN after you've added the indexes you'd get a significantly lower count of rows examined I guess.

If your table already has indexes, you can hint the MySQL server by using a USE INDEX hint.

Samir Selia · Accepted Answer · 2017-08-11T11:20:44.150

Try separating the IN clause. I have personally experienced IN clause making queries slow as internally MySQL assumes full table scan and ignores the index in certain situations. Also, DATE(created_at) will ignore the index completely as it's indexed on datetime.

You can try below UNION query and hope it speeds up the output:

SELECT SUM(total) AS total
  FROM (
    SELECT COUNT(*) AS total
      FROM `table` 
    WHERE `created_at` BETWEEN '2017-08-11 00:00:00' AND  '2017-08-11 23:59:59'
      AND `type` = 4 
      AND `status` = 1
    UNION
    SELECT COUNT(*) AS total
      FROM `table` 
    WHERE `created_at` BETWEEN '2017-08-11 00:00:00' AND  '2017-08-11 23:59:59'
      AND `type` = 13 
      AND `status` = 1
    UNION
    SELECT COUNT(*) AS total
      FROM `table` 
    WHERE `created_at` BETWEEN '2017-08-11 00:00:00' AND  '2017-08-11 23:59:59'
      AND `type` = 15 
      AND `status` = 1
  ) z;

You can also add a column that contains only date or remove time from created_at and store it in a new time column, if feasible for your existing system.

i have syntax error on ``(DATE(`created_at`),`type`,`status`)`` — Saeed Vaziry, Aug 11 '17 at 10:51
sorry my bad. Busy at work so replied in a hurry. Didn't notice the index error. Have updated my post. Do not alter your index. Have modified the query. Try it out. — Samir Selia, Aug 11 '17 at 11:16

score 0 · Answer 3 · answered Aug 11 '17 at 09:51

0

could be you need a proper index eg:

  create index  my_new_index on `table`  ( date(`created_at`), `type`,  `status` );

answered Aug 11 '17 at 09:51

ScaisEdge

131,976
10
91
107

score 0 · Answer 4 · answered Aug 11 '17 at 10:33

Replace

KEY query (created_at,type,status),

KEY query2 (status,type),

with

KEY created_at (created_at),

KEY type (type),

KEY status (status),

I think it will make faster. Also you have to chage datatype of type and status to tinyint, smallint, if it's possible and unsigned constraint should be added it's never signed value.

Mysql execution time is too long

4 Answers4