CODEWITHSUNDEEP
mysql
Alpesh Jikadra
Alpesh Jikadra

Reputation: 1722

Mysql explain query scan more rows then what actual returns

I am using mysql 5.6.22-log

I am executing query on table aggr, with all the condition in where clause.

Following are the data

Table

CREATE TABLE aggr (
 a_date DATE,
 product_id INT(11),
 data_point VARCHAR(16),
 los INT(11),
 hour_0 DOUBLE(4,2),
 UNIQUE KEY `unique_row` (a_date,product_id,data_point,los),
 INDEX product_id(product_id)
);

Insert queries

INSERT INTO aggr(a_date,product_id,data_point,los,hour_0) 
VALUES
('2018-07-29',1,'arrivals',1,10),('2018-07-29',1,'departure',1,9),
('2018-07-29',1,'solds',1,12),('2018-07-29',1,'revenue',1,45.20),
('2018-07-30',1,'arrivals',2,10),('2018-07-30',1,'departure',2,9),
('2018-07-30',1,'solds',2,12),('2018-07-30',1,'revenue',2,45.20),

('2018-07-29',2,'arrivals',1,10),('2018-07-29',2,'departure',1,9),
('2018-07-29',2,'solds',1,12),('2018-07-29',2,'revenue',1,45.20),
('2018-07-30',2,'arrivals',2,10),('2018-07-30',2,'departure',2,9),
('2018-07-30',2,'solds',2,12),('2018-07-30',2,'revenue',2,45.20);

Query

EXPLAIN 
SELECT * FROM aggr
WHERE a_date BETWEEN '2018-07-29' AND '2018-07-29' 
AND product_id = 1 
AND data_point IN('arrivals','departure' ,'solds','revenue') 
AND los = 1 ;

Question

  1. Above query scan 8 rows (while as per the where condition it should scan only 4 rows )

enter image description here

Expected Result :

It should scan only 4 rows instead of 8 rows.

Can some one explain why mysql scan 8 rows instead of 4 rows?

Thanks

Upvotes: 0

Views: 788

Answers (1)

jurez
jurez

Reputation: 4667

EXPLAIN statement is used to obtain information about how the query is executed. The rows number is an approximation only, used by the query optimizer to make decisions when it builds an execution plan. It is a tool for getting diagnostic information by database administration or developer.

What the result of EXPLAIN is actually showing you is that you have no usable index for your query (key is (NULL)). This is quite bad and can cause significant slowdowns for this query. By looking at your table definition, I would say that you need a separate index for data_point, or at least try to make make it the last column for your primary key.

However, none of this is enough to explain the deadlock. I'm not even sure why you are showing us EXPLAIN here - it has nothing to do with it. To be able to diagnose a deadlock, you need to privide more information. Start with the type of your table (MyISAM, InnoDB etc.) and SHOW FULL PROCESSLIST. Then, for each process, see what locks it's holding for each table.

Upvotes: 1

Related Questions