optimizer bug in the index used by mysql/Innodb in the search

Description:
Hello Alexander,

You wrote:

>From EXPLAIN result output you can do only opposite conclusion: With InnoDB MySQL chooses
>to use Index TipoFeVCod for which it expects to match 9417 rows. This is about twice less
>rows than expected with PRIMARY key in second explain 19472

But the fact is that the query

SELECT TIPO,DOC,NRE
FROM GIROS
WHERE (TIPO='R' AND DOC='ZA03003996' AND NRE<'01/01')
OR (TIPO='R' AND DOC<'ZA03003996') OR TIPO<'R'
ORDER BY TIPO DESC, DOC DESC, NRE DESC LIMIT 1

returns only one record (limit 1), and in MyIsam it returns the record in 0.02 secs. while
in innoDB it returns the record in 0.20 secs. (10 times slower) regardless of InnoDB expects
to match only 9417 (I think the optimizer is wrong).

The number of records in the table Giros is 19507. There are 15278 records of tipo='E'
and 4229 of tipo='R'.

I think the optimizer is wrong when it expects to match 9417 rows and the WHERE CONDITION
matches fully whith the PRIMARY INDEX, so I don´t understand why it chooses the other index.

---

On the other hand, let's see the following query which is like the above query:

SELECT TIPO,DOC,NRE
FROM GIROS
WHERE CONCAT(TIPO,DOC,NRE)<='RZA0300399601/01'
ORDER BY TIPO DESC,DOC DESC,NRE DESC LIMIT 1

INNODB/MyIsam Time: 0.02 secs.
INNODB/MyIsam Explain:
table type possible_keys key key_len ref rows Extra
GIROS index NULL PRIMARY 16 NULL 19516 Using where; Using index

Both MyIsam and InnoDB return the record in the same time and use the same index PRIMARY.
This query is the same that the above query.

---

Also, in this query (I deleted the third condition of the where clause TIPO<'R')
SELECT TIPO,DOC,NRE
FROM GIROS
WHERE (TIPO='R' AND DOC='ZA03003996' AND NRE<'01/01')
OR (TIPO='R' AND DOC<'ZA03003996')
ORDER BY TIPO DESC, DOC DESC,NRE DESC limit 1

Both MyIsam and InnoDB use the index PRIMARY.

InnoDB
Time:0.03 secs.
Explain:
table type possible_keys key key_len ref rows Extra
GIROS range PRIMARY,TipoFeVCod PRIMARY 1 Const 1933 Using where; Using index

MyIsam
Time: 0.02 secs.
table type possible_keys key key_len ref rows Extra
GIROS range PRIMARY,TipoFeVCod PRIMARY 16 Null 3910 Using where; Using index

I don´t understand why if I add the third condition: OR TIPO<'R' InnoDB isn´t still
using the PRIMARY INDEX.

---

Finally,

SELECT TIPO,DOC,NRE
FROM GIROS
WHERE (GIROS.TIPO='R' AND GIROS.DOC='ZA03003996' AND GIROS.NRE<'01/01')
OR (GIROS.TIPO='R' AND GIROS.DOC<'ZA03003996') OR GIROS.TIPO<'R'
ORDER BY GIROS.TIPO DESC, GIROS.DOC DESC,GIROS.NRE DESC

There is no LIMIT.

Table type MyIsam:
Returned records: 19486 in 0.59 secs.
Explain:
table type possible_keys key key_len ref rows Extra
GIROS range PRIMARY,TipoFeVCod PRIMARY 16 NULL 19472 Using where; Using index

Table type InnoDb:
Returned records: 19486 in 1.18 secs.
Explain:
table type possible_keys key key_len ref rows Extra
GIROS range PRIMARY,TipoFeVCod TipoFeVCod 1 NULL 9417 Using where; Using index; Using filesort

With InnoDB, the optimizer believes it must examine 9417 records, but actually it returns 19486 records,
the same records as MyIsam but twice slower, so I think the optimizer is okey in MyIsam and wrong in
InnoDB.


Thanks in advance,
Rafa

How-To-Repeat:
Select ... from giros ...

Fix:
-

Synopsis:optimizer bug in the index used by mysql/Innodb in the search

Submitter-Id:
Originator: Rafa
Organization: Pecomark
MySQL support: none
Severity: non-critical
Priority: medium
Category: mysqld-max-nt
Class: sw-bug
Release: mysqld 4.0.11 Gamma(InnoDB)

Exectutable: mysqld-max-nt
Environment: Pentium III-MMX, 500 MHZ, 540 MB
System: Windows 2000
Compiler: -
Architecture: i



____________________________________________________________ ______
The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp

Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/

------------------------------------------------------------ ---------
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
To unsubscribe, e-mail
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php