Re: InnoDB, possible bug?
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 77 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
Re: InnoDB, possible bug?
Andrei, how many rows in ne satisfy (1) ne.playerid=28575, (2) ne.type=2? What version you are using? .48 was tuned to favor index searches over table scans. What does EXPLAIN SELECT say if you force the index usage with USE INDEX and STRAIGHT JOIN clauses? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Andrei Cojocaru [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, May 20, 2002 6:46 PM Subject: InnoDB, possible bug? Hello, I've just switched to InnoDB table from myISAM and it's been running pretty smoothly except on this SQL statement it doesn't use any indexes when there are, and therefore is very slow. mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 734023 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) (Notice that key for ne is NULL when there is obviously an index it could use, but doesn't, why?) the table structures for the two tables are: mysql desc newsentries10; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | newsid| int(10) unsigned| | MUL | 0 | | | playerid | int(10) unsigned| | MUL | 0 | | | hidestamp | int(10) unsigned| | | 0 | | | viewpoint | tinyint(3) unsigned | | | 0 | | | type | tinyint(3) unsigned | | | 0 | | | delmarker | tinyint(3) unsigned | | MUL | 0 | | +---+-+--+-+-+---+ 6 rows in set (0.00 sec) mysql desc pnews; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | newsid| int(10) unsigned| | PRI | NULL| auto_increment | | type | tinyint(3) unsigned | | | 0 | | | id| int(10) unsigned| | MUL | 0 | | | timestamp | int(10) unsigned| | | 0 | | +---+-+--+-+-++ 4 rows in set (0.00 sec) the indexs are: mysql show index from newsentries10; +---++-+--+-+--- +-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++-+--+-+--- +-+--++-+ | newsentries10 | 1 | delmarker |1 | delmarker | A | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |1 | playerid| A | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |2 | type| A | 0 | NULL | NULL | | | newsentries10 | 1 | delete_news |1 | newsid | A | 76379 | NULL | NULL | | +---++-+--+-+--- +-+--++-+ 4 rows in set (0.20 sec) mysql show index from pnews; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | pnews | 0 | PRIMARY |1 | newsid | A | 139047 | NULL | NULL | | | pnews | 1 | id |1 | id | A | 139047 | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 2 rows in set (0.07
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 | +--+ 1 row in set (9.72 sec) mysql select count(*) from newsentries10 where type=2; +--+ | count(*) | +--+ | 611932 | +--+ 1 row in set (9.92 sec) mysql explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.01 sec) mysql mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) mysql mysql explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) Andrei Cojocaru [EMAIL PROTECTED] - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 21, 2002 12:40 PM Subject: Re: InnoDB, possible bug? Andrei, how many rows in ne satisfy (1) ne.playerid=28575, (2) ne.type=2? What version you are using? .48 was tuned to favor index searches over table scans. What does EXPLAIN SELECT say if you force the index usage with USE INDEX and STRAIGHT JOIN clauses? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Andrei Cojocaru [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, May 20, 2002 6:46 PM Subject: InnoDB, possible bug? Hello, I've just switched to InnoDB table from myISAM and it's been running pretty smoothly except on this SQL statement it doesn't use any indexes when there are, and therefore is very slow. mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 734023 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) (Notice that key for ne is NULL when there is obviously an index it could use, but doesn't, why?) the table
Re: InnoDB, possible bug?
Andrei, this is probably not a bug in InnoDB. In theory, 4000 random disk seeks would use more time than scanning the whole table of 700 000 rows. The optimizer was tuned .48 (not yet in 4.0.1) to favor index searches over table scans. That may solve the problem here. On the other hand, the fact that MySQL refuses to use the index specified in the USE INDEX clause may be a bug. I have forwarded this email to MySQL developers. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Andrei Cojocaru [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; Mysql List [EMAIL PROTECTED] Sent: Wednesday, May 22, 2002 3:16 AM 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 | +--+ 1 row in set (9.72 sec) mysql select count(*) from newsentries10 where type=2; +--+ | count(*) | +--+ | 611932 | +--+ 1 row in set (9.92 sec) mysql explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.01 sec) mysql mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) mysql mysql explain select straight_join pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne use index (list_news), pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 774878 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) Andrei Cojocaru [EMAIL PROTECTED] - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 21, 2002 12:40 PM Subject: Re: InnoDB, possible bug? Andrei, how many rows in ne satisfy (1) ne.playerid=28575, (2) ne.type=2? What version you are using? .48 was tuned to favor index searches over table scans. What does EXPLAIN SELECT say if you force the index usage with USE INDEX and STRAIGHT JOIN clauses? Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Original Message - From: Andrei Cojocaru [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, May 20, 2002 6:46 PM Subject: InnoDB, possible bug? Hello, I've just switched to InnoDB table from myISAM and it's been running pretty smoothly except on this SQL statement it doesn't use any indexes when there are, and therefore is very slow
InnoDB, possible bug?
Hello, I've just switched to InnoDB table from myISAM and it's been running pretty smoothly except on this SQL statement it doesn't use any indexes when there are, and therefore is very slow. mysql explain select pn.timestamp,ne.viewpoint,pn.id,pn.type,ne.type,ne.newsid,ne.hidestamp from newsentries10 ne, pnews pn where ne.playerid=28575 and ne.type=2 and pn.newsid=ne.newsid; +---++---+-+-+---+-- --++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+---+-- --++ | ne| ALL| list_news,delete_news | NULL|NULL | NULL | 734023 | where used | | pn| eq_ref | PRIMARY | PRIMARY | 4 | ne.newsid | 1 || +---++---+-+-+---+-- --++ 2 rows in set (0.00 sec) (Notice that key for ne is NULL when there is obviously an index it could use, but doesn't, why?) the table structures for the two tables are: mysql desc newsentries10; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | newsid| int(10) unsigned| | MUL | 0 | | | playerid | int(10) unsigned| | MUL | 0 | | | hidestamp | int(10) unsigned| | | 0 | | | viewpoint | tinyint(3) unsigned | | | 0 | | | type | tinyint(3) unsigned | | | 0 | | | delmarker | tinyint(3) unsigned | | MUL | 0 | | +---+-+--+-+-+---+ 6 rows in set (0.00 sec) mysql desc pnews; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | newsid| int(10) unsigned| | PRI | NULL| auto_increment | | type | tinyint(3) unsigned | | | 0 || | id| int(10) unsigned| | MUL | 0 || | timestamp | int(10) unsigned| | | 0 || +---+-+--+-+-++ 4 rows in set (0.00 sec) the indexs are: mysql show index from newsentries10; +---++-+--+-+--- +-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++-+--+-+--- +-+--++-+ | newsentries10 | 1 | delmarker |1 | delmarker | A | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |1 | playerid| A | 0 | NULL | NULL | | | newsentries10 | 1 | list_news |2 | type| A | 0 | NULL | NULL | | | newsentries10 | 1 | delete_news |1 | newsid | A | 76379 | NULL | NULL | | +---++-+--+-+--- +-+--++-+ 4 rows in set (0.20 sec) mysql show index from pnews; +---++--+--+-+---+-- ---+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-- ---+--++-+ | pnews | 0 | PRIMARY |1 | newsid | A | 139047 | NULL | NULL | | | pnews | 1 | id |1 | id | A | 139047 | NULL | NULL | | +---++--+--+-+---+-- ---+--++-+ 2 rows in set (0.07 sec) Now I have an index on the two columns that are used in the query on the newsentries10 table and it doesn't use them at all. Could someone please explain this to me and give me any advice on how to fix it? Thanks for your help in advance. words to bypass filter: sql queries Andrei Cojocaru [EMAIL PROTECTED] - 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?
