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'
