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
