MySQL query run time is better even though its execution plan is bad

user_584154 0
Viewed 249

I am trying to optimize this MySQL query and having less experience in understanding execution plan I am having hard time making sense of the execution plan.

My question is : Can you please help me in understanding why the query execution plan of New Query is worse than that of Original query even though New query performs better in Prod.

SQL needed to reproduce this case is here
Also kept relevant table definition in the end ( Table bill_range references bill using foreign key bill_id )

Original query takes 10 second to complete in PROD

select * 
from bill_range 
where (4050 between low and high ) 
order by bill_id limit 1;

while new query (I am forcing/suggesting to use index) takes 5 second to complete in PROD

select * 
from bill_range 
use index ( bill_range_low_high_index) 
where (4050 between low and high ) 
order by bill_id limit 1;

But the execution plan gives suggest original query is better( this is the part where my understanding seems to be wrong )

Original query enter image description here

New query enter image description here

  1. Column "type" for original query suggest index while new query says ALL
  2. Column "Key" is bill_id (perhaps index on FK) for original queryand Null for new query
  3. Column "rows" for original query is 1 while for new query says 9

So given all this information wouldn't it imply that new query is actually worse than original query . And if that is true why is new query performing better? Or am I reading the execution plan wrong.

Table defintions

CREATE TABLE bill_range (
    id int(11) NOT NULL AUTO_INCREMENT,
    low varchar(255) NOT NULL,
    high varchar(255) NOT NULL,
    PRIMARY KEY (id),
    bill_id int(11) NOT NULL,
    FOREIGN KEY (bill_id) REFERENCES bill(id)
);
CREATE TABLE bill (
    id int(11) NOT NULL AUTO_INCREMENT,
    label varchar(10),
    PRIMARY KEY (id)
);
create index bill_range_low_high_index on bill_range( low, high);

NOTE : The reason I am providing definition of 2 tables is because original query decided to use an index based on Foreign key to bill table

indexing query-optimization mysql sql
1 Answers

Your index isn't quite optimal for your query. Let me explain if I may.

MySQL indexes use BTREE data structures. Those work well in indexed-sequential access mode (hence the MyISAM name of MySQL's first storage engine). It favors queries that jump to a particular place in an index and then run through the index element by element. The typical example is this, with an index on col.

 SELECT whatever FROM tbl WHERE col >= constant AND col <= constant2

That is a rewrite of WHERE col BETWEEN constant AND constant2.

Let's recast your query so this pattern is obvious, and so the columns you want are explicit.

select id, low, high, bill_id 
from bill_range 
where low <= 4050 
  and high >= 4050 
order by bill_id limit 1;

An index on the high column allows a range scan starting with the first eligible row with high >= 4050. Then, we can go on to make it a compound index, including the bill_id and low columns.

CREATE INDEX high_billid_low ON bill_range (high, bill_id, low);

Because we want the lowest matching bill_id we put that into the index next, then finally the low value. So the query planner random accesses the index to the first elibible row by high, then scans until it finds the very first index item that meets the low criterion. And then it's done: that's the desired result. It's already ordered by bill_id so it can stop. ORDER BY comes from the index. The query can be satisfied entirely from the index -- it is a so-called covering index.

As to why your two queries performed differently: In the first, the query planner decided to scan your data in bill_id order looking for the first matching low/high pair. Possibly it decided that actually sorting a result set would likely be more expensive than scanning bill_ids in order. It looks to me like your second query did a table scan. Why that was faster, who knows?

Notice that this index would also work for you.

CREATE INDEX low_billid_high ON bill_range (low DESCENDING, bill_id, high);

In InnoDB the table's PK id is implicitly part of every index, so there's no need to mention it in the compound index.

And, you can still write it the way you first wrote it; the query planner will figure out what you want.

Pro tip: Avoid SELECT * ... the * makes it harder to reason about the columns you need to retrieve.

user_2056080
Related