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
