CODEWITHSUNDEEP
mysqlsqloptimizationbetween
Jusee
Jusee

Reputation: 1

SQL Between Clause too slow

Thank you for your assistance, we have an SQL query that ran for 5 years and it was fine. With new load coming in, the query took more than 4 hours to run and it timed out, we isolated the problem to this statement:

This SQL Statement was simplified for easy reading:

select * from table1 t1, table2 t2
where (t1.ProductCode between t2.BeginNumber and t2.EndNumber)

Example with values
select * from table1 t1, table2 t2
where ('12345' between '12345' and '12345')

ProductCode, BeginNumber, EndNumber has a BTree Index, and they are varchar.

Table1 has 50000 rows Table2 has 113000 rows

BeginNumber and EndNumber are actually the same number, there is no range in them (example BeginNumber=12345 EndNumber=12345), but for compliance sake we can't change Between Clause to Equals Clause because they may change the numbers to a range in future.

This runs in MYSQL 5.6.11, configured InnoDB Memory 4GB, Intel i7-8550, 8GB RAM It was designed to be a small non critical database, but recent changes from incoming data source has grown significantly and the jobs are hanging, and it hangs at this statement for too long and time out (4 hours).

Hardware will be upgraded with 16G RAM, SSD, other than that, how can we make the statement run faster?

Upvotes: 0

Views: 5009

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You need to check the explain plans to see what is happening.

First, are the types exactly the same. This is very important. The collation matters if these are strings . . . your individual query suggests they are strings. Your naming suggests otherwise.

Second, for this query:

select *
from table1 t1 join
     table2 t2
     on t1.ProductCode between t2.BeginNumber and t2.EndNumber

The only appropriate index is on t1(productcode). MySQL might have trouble picking up on its usage. So it might be better to phrase the query as:

select *
from table2 t2 join
     table1 t1
     on t2.BeginNumber <= t1.ProductCode and
        t2.EndNumber >= t1.ProductCode;

This makes it clear that table2 is going to be the "driving" table in the join. That is, MySQL will scan table2 and then look for the appropriate matching rows in t1.

As a note: Your example with values has nothing to do with your first query. That example processes the where clause during the compilation phase and notes that it is always true. It then proceeds to do a cross join. It will return all possible pairs from the two tables. You probably see it as running fast because you are looking at the first row being returned rather than waiting for all possible combinations.

Upvotes: 1

LukStorms
LukStorms

Reputation: 29667

Sometimes it helps to change a BETWEEN to 2 criteria.

And while changing the query, might as well go with the times and use modern JOIN syntax. 

SELECT * 
FROM table1 t1
JOIN table2 t2 
  ON (t2.BeginNumber <= t1.ProductCode AND t2.EndNumber >= t1.ProductCode)

And it probably wouldn't hurt to do an ANALYZE TABLE on both tables.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You shoud check for proper index 

select * 
from table1 t1
INNER  table2 t2 ON t1.ProductCode  between  t2.BeginNumber and t2.EndNumber

Be sure you have proper index 

table1 column  ProductCode

and table 

table2 composite index  on columns  (BeginNumber, EndNumber)

Upvotes: 0

Related Questions