CODEWITHSUNDEEP
mysqlsqlfull-text-search
Rajat Jain
Rajat Jain

Reputation: 2032

MYSQL OR query problem (scans full table even when using indexes)

I am using EXPLAIN to get performance analysis of my below query:

SELECT `wf_cart_items` . `id`
FROM `wf_cart_items`
WHERE (`wf_cart_items` . `docket_number` = '405-2844' OR
       match( `wf_cart_items` . `multi_docket_number` ) against ( '405-2844' )
      )

enter image description here

The problem is that it shows rows to be searched 597151 while individual OR queries examine only 1 row each. How is it possible that when I use OR it is doing a full table scan?

d

enter image description here

P.S.: I have FULL-TEXT index on multi_docket_number & BTREE index on docket_number

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270833

OR is quite tricky for SQL optimizers -- both in the WHERE clause and in ON clauses.

The recommendation is to switch this to union all:

SELECT ci.id
FROM wf_cart_items ci
WHERE ci.docket_number = '405-2844' 
UNION ALL
SELECT ci.id
FROM wf_cart_items ci
WHERE MATCH(ci.multi_docket_number) AGAINST ( '405-2844' ) AND
      ci.docket_number <> '405-2844';

Based on the naming of your columns, I feat that multi-docket_number actually contains multiple docket numbers. If that is the case, you probably want to fix the data model, but that is another conversation.

Upvotes: 1

Related Questions