CODEWITHSUNDEEP
mysqlsql
Sanjay Dutta
Sanjay Dutta

Reputation: 1

Optimizing MySql Query?

Hi is there any way of optimizing this mysql query ?

SELECT ADDRESS , D_ADDRESS , U_DATA 
FROM DATABASE1.TABLE1 
WHERE (ADDRESS LIKE '0010') OR 
(ADDRESS LIKE 'DHIL') OR 
(ADDRESS LIKE 'FINN') OR 
(ADDRESS LIKE 'GRWL') OR 
(ADDRESS LIKE 'JNOD') OR 
...

Upvotes: 0

Views: 81

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You should use in and create an appropriate index. The in version (as in other answers) looks like:

SELECT ADDRESS , D_ADDRESS , U_DATA 
FROM DATABASE1.TABLE1 
WHERE ADDRESS IN ('0010', . . .)

The index you want is:

create index idx_table1_address on table1(address);

It is the index that will give the bigger boost performance, if the optimizer decides that it is useful. However, MySQL does optimize in with a list of constants by sorting the list and doing a binary search.

Upvotes: 0

i486
i486

Reputation: 6564

  1. Create additional table with column search of type CHAR(4) and fill it will all search values: 0010, DHIL, ...

  2. Run query:

    SELECT ADDRESS , D_ADDRESS , U_DATA FROM DATABASE1.TABLE1 WHERE ADDRESS IN (SELECT search FROM search_table)

or

SELECT t.ADDRESS, t.D_ADDRESS, t.U_DATA
  FROM DATABASE1.TABLE1 t JOIN search_table s ON t.ADDRESS=s.search

Upvotes: 0

Nikos M.
Nikos M.

Reputation: 8325

try REGEXP e.g 

WHERE ADDRESS REGEXP '(0010)|(DHIL)|(..)'

involves only one test which is faster

Also if you want just accurate matches you may employ the FIELD MySQL function like:

WHERE FIELD(ADDRESS,'0010|DHIL|..') > 0

or even

WHERE FIELD(ADDRESS,'0010','DHIL','..') > 0

Upvotes: 1

Shadow
Shadow

Reputation: 34232

Since you are not using any wildcards in the selection criteria, all like comparisons mean equal. Therefore you can use the in() operator instead of the ORs:

SELECT ADDRESS , D_ADDRESS , U_DATA 
FROM DATABASE1.TABLE1 
WHERE ADDRESS IN ('0010', 'DHIL', ...)

Upvotes: 2

Related Questions