Try a union instead of an or condition.
http://dev.mysql.com/doc/refman/5.0/en/union.html
Johnny Withers wrote:
I have the following tables:
Customer: id,ssn
Customer_Id: id,customer_id,id_num
The customer table holds customers along with their SSN and the customer_id
table holds identifications for each customer (Driver's License, State
Issued ID, Student ID, etc). The SSN column from the customer table is
VARCHAR(9) and the id_num column from the customer_id table is VARCHAR(32).
Both of these columns have an index on them.
The following query uses the index on customer.ssn and executes in 0ms:
SELECT SQL_NO_CACHE customer.id,customer.ssn,customer_id,id_num
FROM customer USE INDEX(idx_ssn)
LEFT JOIN customer_id ON customer.id=customer_id.customer_id
WHERE ssn='123456789';
Explain output:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: ref
possible_keys: idx_ssn
key: idx_ssn
key_len: 35
ref: const
rows: 1
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: customer_id
type: ref
possible_keys: customer_key
key: customer_key
key_len: 5
ref: aca_ecash.customer.id
rows: 1
Extra:
Now, this is the query I have trouble with, it does not use the index (or
says it does but doesn't?) and on a busy system (200+ queries per sec) can
take up to 20 seconds or more to execute:
SELECT SQL_NO_CACHE customer.id,customer.ssn,customer_id,id_num
FROM customer USE INDEX(idx_ssn)
LEFT JOIN customer_id ON customer.id=customer_id.customer_id
WHERE ssn='123456789' OR id_num='123456789';
Explain output:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: index
possible_keys: idx_ssn
key: idx_ssn
key_len: 35
ref: NULL
rows: 165843
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: customer_id
type: ref
possible_keys: customer_key
key: customer_key
key_len: 5
ref: aca_ecash.customer.id
rows: 1
Extra: Using where
Is there some way I can make it use the index? I've thought about
redesigning the query to select from the customer_id table first, if a row
is found.. just return the matching customer_id from the customer table..
but I wanted to see if maybe i'm going about this the wrong way before I
"engineer" some way around this.
Thanks in advance,
-----------------------------
Johnny Withers
601.209.4985
[email protected]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
