Do you have an index on id_num? What sort of explain output do you get when you don't use a query hint? Your USE INDEX hint may be causing MySQL to ignore a better strategy.
If you have separate indexes on ssn and id_num, MySQL may be able to efficiently use an index merge optimization . See http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html. This is only in 5.0+ - on older versions of MySQL you may find a union more efficient. On Mon, Jan 12, 2009 at 9:43 AM, Johnny Withers <joh...@pixelated.net> 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 > joh...@pixelated.net > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
