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
