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