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. > > > > > > 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? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > > > --------------------------------------------------------------------- > > 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 > > > > --------------------------------------------------------------------- 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
