BTW, I assume Monty's change means that MyISAM tables are able to not use filesorting on "WHERE key_name1='constant' ORDER BY key_name DESC " type queries whereas InnoDB tables are not able to do so ?
e.g. : mysql> ALTER TABLE threadhardwarefr13 type=innodb; mysql> EXPLAIN SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE numeropost='18' ORDER BY numreponse DESC LIMIT 0,3; +--------------------+------+---------------+---------+---------+-------+--- ---+----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------------+------+---------------+---------+---------+-------+--- ---+----------------------------+ | threadhardwarefr13 | ref | PRIMARY | PRIMARY | 3 | const | 39 | where used; Using filesort | +--------------------+------+---------------+---------+---------+-------+--- ---+----------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE threadhardwarefr13 type=myisam; Query OK, 1387 rows affected (3.54 sec) Records: 1387 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr13 WHERE numeropost='18' ORDER BY numreponse DESC LIMIT 0,3; +--------------------+------+---------------+---------+---------+-------+--- ---+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------------+------+---------------+---------+---------+-------+--- ---+------------+ | threadhardwarefr13 | ref | PRIMARY | PRIMARY | 3 | const | 77 | where used | +--------------------+------+---------------+---------+---------+-------+--- ---+------------+ 1 row in set (0.00 sec) Regards, Jocelyn ----- Original Message ----- From: "Jocelyn Fournier" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, September 26, 2002 8:53 PM Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180... > Heikki, Jeremy, > > FYI, I've just tested latest pull (including Monty's patch) without Heikki > 1.1318 changeset (Remove the flag HA_NOT_READ_PREFIX_LAST because ORDER BY > orders wrong then;) and now it works perfectly fine : > > mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE > numeropost='0' ORDER BY numreponse ASC LIMIT 0,3; > +---------------+---------------------+------------+ > | LOWER(pseudo) | date | numreponse | > +---------------+---------------------+------------+ > | kytine | 2002-09-18 20:37:31 | 1360 | > | joce | 2002-09-18 20:42:20 | 1361 | > | kytine | 2002-09-18 20:46:32 | 1362 | > +---------------+---------------------+------------+ > 3 rows in set (0.82 sec) > > mysql> SELECT LOWER(pseudo),date,numreponse FROM threadhardwarefr12 WHERE > numeropost='0' ORDER BY numreponse DESC LIMIT 0,3; > +---------------+---------------------+------------+ > | LOWER(pseudo) | date | numreponse | > +---------------+---------------------+------------+ > | kytine | 2002-09-18 20:46:32 | 1362 | > | joce | 2002-09-18 20:42:20 | 1361 | > | kytine | 2002-09-18 20:37:31 | 1360 | > +---------------+---------------------+------------+ > 3 rows in set (0.00 sec) > > So it seems ORDER BY + WHERE primarykey LIKE 'abc%' ORDER BY primarykey DESC > works fine now. > > Regards, > Jocelyn > ----- Original Message ----- > From: "Heikki Tuuri" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Cc: "Jocelyn Fournier" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > Sent: Thursday, September 26, 2002 8:39 PM > Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180... > > > > Jeremy, > > > > ----- Original Message ----- > > From: "Jeremy Zawodny" <[EMAIL PROTECTED]> > > To: "Heikki Tuuri" <[EMAIL PROTECTED]> > > Cc: "Jocelyn Fournier" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; > > <[EMAIL PROTECTED]> > > Sent: Thursday, September 26, 2002 8:25 PM > > Subject: Re: InnoDB: Assertion failure in file ha_innodb.cc line 2180... > > > > > > > On Wed, Sep 25, 2002 at 09:43:13PM +0300, Heikki Tuuri wrote: > > > > Jocelyn, > > > > > > > > below the latest patch which puts the code as it was in 4.0.3. Some > LIKE > > > > 'abc%' ... DESC queries may return wrong results, but this is the best > > we > > > > can get to 4.0.4. > > > > > > > > I have to ask Monty about the use of HA_READ_PREFIX_LAST. Other bugs > may > > be > > > > lurking in the use/non-use of that search flag. > > > > > > FYI, my server is happy now too. > > > > thank you for testing the source tree! Now we found and fixed these bugs > > before the actual release was made :). > > > > One bug remains: I tested yesterday that > > > > WHERE primarykey LIKE 'abc%' ORDER BY primarykey DESC; > > > > really works wrong in upcoming InnoDB-4.0.4. It only returns rows where > > primarykey = 'abc'. But better fix that bug in 4.0.5 since it requires a > > somewhat more fundamental change. Until that users beware! > > > > > I'll rebuild with Monty's latest patch and see that things work as > > > expected. > > > > > > Thanks! > > > > > > Jeremy > > > > Thank you, > > > > Heikki > > Innobase Oy > > > > > > > > > -- > > > Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! > > > <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ > > > > > > MySQL 3.23.51: up 51 days, processed 1,083,975,227 queries (244/sec. > avg) > > > > > > > > > > > --------------------------------------------------------------------- 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
