Hi all.
I want to optimize a query that is executed often in the application we use
at work.
NOTE: Excuse me for using spanish names for the tables and the columns.
In the database we have three tables for article: ARTICULOINFO (information
of an article), LARTICULOINFO_IDIOMA (information of an article that depends
of the language, we use it for the locals we have in Portugal) and
LARTICULOINFO_PVP (information
of the price of an article for a determinated local).
When the language of the application is spanish the query is
SELECT ART.*, IFNULL(ART_PVP.PVP, ART.PVP) AS PVPREAL
FROM ARTICULOINFO ART LEFT JOIN LARTICULOINFO_PVP ART_PVP ON (ART.ID
= ART_PVP.IDARTICULOINFO
AND ART_PVP.FECHAINICIO <= '2001-05-29' AND ART_PVP.FECHAFINAL
>= '2001-05-29' AND ART_PVP.IDLOCAL = 'LOC:0:0')
WHERE ART.descripcion LIKE '%teka%'
Otherwise the query is:
SELECT ART.*, IFNULL(ART_PVP.PVP, ART_IDI.PVP) AS PVPREAL
FROM LARTICULOINFO_IDIOMA ART_IDI, ARTICULOINFO ART LEFT JOIN
LARTICULOINFO_PVP ART_PVP ON (
ART.ID = ART_PVP.IDARTICULOINFO
AND ART_PVP.FECHAINICIO <= '2001-05-29' AND ART_PVP.FECHAFINAL
>= '2001-05-29'
AND ART_PVP.IDLOCAL = 'LOC:0:0'
)
WHERE ART.descripcion LIKE '%teka%'
NOTE: This is an example of the query looking for the description of the
article.
When I use the explain command whith the first query it responds:
mysql> explain SELECT ART.*, IFNULL(ART_PVP.PVP, ART.PVP) AS PVPREAL
FROM ARTICULOINFO ART LEFT JOIN LARTICULOINFO_PVP ART_PVP ON (ART.ID
= ART_PVP.IDARTICULOINFO
AND ART_PVP.FECHAINICIO <= '2001-05-29' AND ART_PVP.FECHAFINAL
>= '2001-05-29' AND ART_PVP.IDLOCAL = 'LOC:0:0')
WHERE ART.descripcion LIKE '%teka%';
+---------+-------+---------------------------------------------------------
----+---------+---------+------+-------+------------+
| table | type | possible_keys
| key | key_len | ref | rows | Extra |
+---------+-------+---------------------------------------------------------
----+---------+---------+------+-------+------------+
| ART | ALL | NULL
| NULL | NULL | NULL | 33847 | where used |
| ART_PVP | range |
idArticuloInfo,idLocal,fechaInicio,fechaFinal,join_articulo | idLocal |
26 | NULL | 1 | where used |
+---------+-------+---------------------------------------------------------
----+---------+---------+------+-------+------------+
2 rows in set (0.00 sec)
The index for LARTICULOINFO_PVP are:
mysql> show index from LARTICULOINFO_PVP;
+-------------------+------------+----------------+--------------+----------
------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+-------------------+------------+----------------+--------------+----------
------+-----------+-------------+----------+--------+---------+
| LARTICULOINFO_PVP | 0 | PRIMARY | 1 | id
| A | 1 | NULL | NULL | NULL |
| LARTICULOINFO_PVP | 1 | idArticuloInfo | 1 |
idArticuloInfo | A | 1 | NULL | NULL | NULL |
| LARTICULOINFO_PVP | 1 | idLocal | 1 | idLocal
| A | 1 | NULL | NULL | NULL |
| LARTICULOINFO_PVP | 1 | fechaInicio | 1 |
fechaInicio | A | 1 | NULL | NULL | NULL |
| LARTICULOINFO_PVP | 1 | fechaFinal | 1 |
fechaFinal | A | 1 | NULL | NULL | NULL |
| LARTICULOINFO_PVP | 1 | join_articulo | 1 | idLocal
| A | 1 | NULL | NULL | NULL |
| LARTICULOINFO_PVP | 1 | join_articulo | 2 |
idArticuloInfo | A | 1 | NULL | NULL | NULL |
| LARTICULOINFO_PVP | 1 | join_articulo | 3 |
fechaInicio | A | 1 | NULL | NULL | NULL |
| LARTICULOINFO_PVP | 1 | join_articulo | 4 |
fechaFinal | A | 1 | NULL | NULL | NULL |
+-------------------+------------+----------------+--------------+----------
------+-----------+-------------+----------+--------+---------+
9 rows in set (0.00 sec)
I've added the index join_articulo for the columns idLocal, idArticuloInfo,
fechaInicio and fechaFinal expecting that the query will use this index but
it didn't. Instead it uses index idLocal. I think it will use index
jon_articulo because it has the four columns in the condition of the left
join.
It's is in a MyIsam database of mysql 3.23.27 we use for test but in the
production database (Isam tables in mysql 3.22.30) it's worse the explain
comand responds:
+---------+-------+---------------------------------------------------------
----+------+---------+------+-------+------------+
| table | type | possible_keys
| key | key_len | ref | rows | Extra |
+---------+-------+---------------------------------------------------------
----+------+---------+------+-------+------------+
| ART | ALL | NULL
| NULL | NULL | NULL | 33847 | where used |
| ART_PVP | range |
idArticuloInfo,idLocal,fechaInicio,fechaFinal,join_articulo | NULL | NULL
| NULL | 1 | where used |
+---------+-------+---------------------------------------------------------
----+------+---------+------+--------------------+
2 rows in set (0.15 sec)
Thanks in advance.
Iago.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
