List:General Discussion« Previous MessageNext Message »
From:Jon Petersen Date:June 19 2002 7:35pm
Subject:Why doesn't MySQL use an index for the following query? Because of the 'or'. But how can I change this behaviour?
View as plain text  
Dear all,

Please help on the following problem:

2 tables: product and product_description. Join: product.prod =
product_descriptiond.prod or product.prodlevid =
product_description.prod (1 table is joined to the other on column a or
column b); this join doesn't use any of the available indexes.... Why?


Available Indexes
=================
mysql> show index from product;
+---------+------------+---------------+--------------+-------------+---
--------+-------------+----------+--------+---------+
| Table   | Non_unique | Key_name      | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+---------+------------+---------------+--------------+-------------+---
--------+-------------+----------+--------+---------+
| product |          1 | i_pNaam       |            1 | Name        | A
|       31826 |     NULL | NULL   | NULL    |
| product |          1 | i_pProdLevId  |            1 | ProdLevId   | A
|       31826 |     NULL | NULL   | NULL    |
| product |          1 | idx_catstruct |            1 | CatStruct   | A
|         148 |     NULL | NULL   | NULL    |
| product |          0 | PRIMARY       |            1 | ProdLevId   | A
|        NULL |     NULL | NULL   | NULL    |
| product |          0 | PRIMARY       |            2 | supplier    | A
|        NULL |     NULL | NULL   | NULL    |
| product |          1 | ppp4          |            1 | ProdId      | A
|       31826 |     NULL | NULL   | NULL    |
| product |          1 | ppp5          |            1 | ProdLevId   | A
|       31826 |     NULL | NULL   | NULL    |
+---------+------------+---------------+--------------+-------------+---
--------+-------------+----------+--------+---------+
7 rows in set (0.00 sec)

mysql> show index from product_description;
+---------------------+------------+---------------+--------------+-----
--------+-----------+-------------+----------+--------+---------+
| Table               | Non_unique | Key_name      | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------------------+------------+---------------+--------------+-----
--------+-----------+-------------+----------+--------+---------+
| product_description |          0 | PRIMARY       |            1 |
ProdLevId   | A         |        1741 |     NULL | NULL   | NULL    |
| product_description |          1 | i_paProdLevId |            1 |
ProdLevId   | A         |        NULL |     NULL | NULL   | NULL    |
+---------------------+------------+---------------+--------------+-----
--------+-----------+-------------+----------+--------+---------+
2 rows in set (0.00 sec)

Select on 1 column (this is oke)
================================
mysql> explain select p.prodlevid from product p, product_description pd
where p.prodid = pd.prodlevid;
+-------+-------+-----------------------+---------+---------+-----------
---+------+-------------+
| table | type  | possible_keys         | key     | key_len | ref
| rows | Extra       |
+-------+-------+-----------------------+---------+---------+-----------
---+------+-------------+
| pd    | index | PRIMARY,i_paProdLevId | PRIMARY |      75 | NULL
| 1741 | Using index |
| p     | ref   | ppp4                  | ppp4    |      31 |
pd.ProdLevId |    1 | where used  |
+-------+-------+-----------------------+---------+---------+-----------
---+------+-------------+
2 rows in set (0.00 sec)

Select on 2 columns (NOT oke)
=============================
mysql> explain select p.prodlevid from product p, product_description pd
where p.prodid = pd.prodlevid or p.prodlevid = pd.prodlevid;
+-------+-------+--------------------------------+---------+---------+--
----+-------+-------------+
| table | type  | possible_keys                  | key     | key_len |
ref  | rows  | Extra       |
+-------+-------+--------------------------------+---------+---------+--
----+-------+-------------+
| pd    | index | PRIMARY,i_paProdLevId          | PRIMARY |      75 |
NULL |  1741 | Using index |
| p     | ALL   | i_pProdLevId,PRIMARY,ppp4,ppp5 | NULL    |    NULL |
NULL | 31826 | where used  |
+-------+-------+--------------------------------+---------+---------+--
----+-------+-------------+
2 rows in set (0.00 sec)

Any help???

Regards,


Jon Petersen
ICEshop BV
+31 30 2735486
----------------------------------
jonp@stripped
jon@stripped
----------------------------------
http://www.iceshop.nl
http://www.iceshop.nl/documentatie
http://www.iceshop.nl/faq
----------------------------------
N 52° 04.968' E 05° 03.933'
 

Thread
Why doesn't MySQL use an index for the following query? Because of the 'or'. But how can I change this behaviour?Jon Petersen19 Jun
  • Re: Why doesn't MySQL use an index for the following query? Becauseof the 'or'. But how can I change this behaviour?Harrison C. Fisk19 Jun
    • Re: Why doesn't MySQL use an index for the following query? Because of the 'or'. But how can I change this behaviour?Keith C. Ivey19 Jun
    • Re: Why doesn't MySQL use an index for the following query? Because of the 'or'. But how can I change this behaviour?Jon Petersen19 Jun