Hi!
>>>>> "Heikki" == Heikki Tuuri <[EMAIL PROTECTED]> writes: Heikki> Andrei, Heikki> this is probably not a bug in InnoDB. In theory, 4000 random disk seeks Heikki> would use more time than scanning the whole table of 700 000 rows. Heikki> The optimizer was tuned .48 (not yet in 4.0.1) to favor index searches over Heikki> table scans. That may solve the problem here. Heikki> On the other hand, the fact that MySQL refuses to use the index specified in Heikki> the USE INDEX clause may be a bug. I have forwarded this email to MySQL Heikki> developers. USE INDEX ... only tells MySQL that it should only consider using one of the named index to resolve the query. MySQL is however still free to use a table scan if finds the given index not suitable for resolving the query. Heikki> Best regards, Heikki> Heikki Tuuri Heikki> Innobase Oy Heikki> --- Heikki> Order technical MySQL/InnoDB support at https://order.mysql.com/ Heikki> See http://www.innodb.com for the online manual and latest news on InnoDB Heikki> ----- Original Message ----- Heikki> From: "Andrei Cojocaru" <[EMAIL PROTECTED]> Heikki> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; "Mysql List" Heikki> <[EMAIL PROTECTED]> Heikki> Sent: Wednesday, May 22, 2002 3:16 AM Heikki> Subject: Re: InnoDB, possible bug? >> I am using mysql 4.0.1-alpha on Linux 2.4.18, the info you requested is: >> select count(*) from newsentries10 where playerid=28575 and type=2; >> +----------+ >> | count(*) | >> +----------+ >> | 4218 | >> +----------+ >> 1 row in set (13.81 sec) >> mysql> select count(*) from newsentries10 where playerid=28575 and type=2; >> +----------+ >> | count(*) | >> +----------+ >> | 3705 | >> +----------+ Do you know why the result differs in this case ? mysql> explain select straight_join >> pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp Heikki> from >> newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and >> pn.newsid=ne.newsid; Heikki> +-------+--------+-----------------------+---------+---------+-----------+-- >> ------+------------+ >> | table | type | possible_keys | key | key_len | ref | >> rows | Extra | >> Heikki> +-------+--------+-----------------------+---------+---------+-----------+-- >> ------+------------+ >> | ne | ALL | list_news,delete_news | NULL | NULL | NULL | >> 774878 | where used | >> | pn | eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | >> 1 | | >> Heikki> +-------+--------+-----------------------+---------+---------+-----------+-- > show index from newsentries10; <cut> >> > > | newsentries10 | 1 | list_news | 1 | playerid >> | >> > A >> > > | 0 | NULL | NULL | | >> > > | newsentries10 | 1 | list_news | 2 | type <cut> Heikki, something is a bit strange here. In this case MySQL will ask the InnoDB table handler of how many rows matches the key range (ne.playerid,ne.type) [28575, 2] In this case, InnoDB should return about 4000 rows, but it appears that it returns 770000 rows. "Andrei", could you upload a copy of the tables to ftp://support.mysql.com/pub/mysql/secret so that Heikki could test this ? Regards, Monty --------------------------------------------------------------------- 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
