From: Johnny Withers (johnnypixelated.net)
Date: Wed Jan 14 2009 - 17:34:15 CST

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

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.garnergmail.com>wrote:

> On Tue, Jan 13, 2009 at 7:07 PM, Baron Schwartz <baronxaprb.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
johnnypixelated.net

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