From: Ken Menzel (kenmysqlicarz.com)
Date: Tue Jan 13 2009 - 09:24:45 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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
> johnnypixelated.net
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]