The index hint is not in productoin code.. I was trying ot force it to use
the index even when using the OR clause.. ment to take that out before I
sent the email.
The table structure is:
CREATE TABLE `customer` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ssn` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_ssn` (`ssn`)
) ENGINE=InnoDB
CREATE TABLE `customer_id` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`customer_id` int(10) unsigned DEFAULT NULL,
`id_num` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `customer_key` (`customer_id`),
KEY `id_id_num` (`id_num`)
) ENGINE=InnoDB
The explain output of the query using the OR clause:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: ALL
possible_keys: idx_ssn
key: NULL
key_len: NULL
ref: NULL
rows: 176680
Extra: Using where; Using temporary
*************************** 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; Distinct
2 rows in set (0.00 sec)
Using a UNION results in:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: customer
type: range
possible_keys: idx_ssn
key: idx_ssn
key_len: 35
ref: NULL
rows: 1
Extra: Using where; Using temporary
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: customer_id
type: ref
possible_keys: customer_key
key: customer_key
key_len: 5
ref: aca_ecash.customer.id
rows: 1
Extra: Using index; Distinct
*************************** 3. row ***************************
id: 2
select_type: UNION
table: customer_id
type: range
possible_keys: customer_key,id_id_num
key: id_id_num
key_len: 35
ref: NULL
rows: 1
Extra: Using where; Using temporary
*************************** 4. row ***************************
id: 2
select_type: UNION
table: customer
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: aca_ecash.customer_id.customer_id
rows: 1
Extra: Using where
*************************** 5. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
5 rows in set (0.01 sec)
The union is much faster. I've tested the same search for ID numbers on our
test system (Windows 32-Bit, 2GB ram, P4 3Ghz) against the productoin system
(RHEL 64-Bit 16GB ram, Dual Xeon 2Ghz).. the search in the test system is
almost instant as compared to the production system its taking 4 to 6
seconds. There's not much traffic today on it.
I'm going to put the UNION into production and see how it goes.
Thanks for the replies.
-johnny
On Tue, Jan 13, 2009 at 7:39 PM, Andrew Garner <andrew.b.gar...@gmail.com>wrote:
> On Tue, Jan 13, 2009 at 7:07 PM, Baron Schwartz <ba...@xaprb.com> wrote:
> >> 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.
> >
> > And in newer versions, too. The optimizer frequently underestimates
> > the cost of the merge operation and the required random I/O for row
> > lookups. So, yes it can "use" an index merge, but... efficiency is
> > another question. I've seen table scans outperform a two-way index
> > merge by orders of magnitude.
> >
> These appeared to be high selectivity indexes, but perhaps I assumed
> too much. :)
>
--
-----------------------------
Johnny Withers
601.209.4985
joh...@pixelated.net
