MySQL Forums
Forum List  »  MyISAM

query scanning all rows
Posted by: jay oberoi
Date: September 12, 2012 01:20PM

Guys trying to wrap my head around mysql query on why it is scanning all rows in table

I have 2 tables topic_entry and topic_user

CREATE TABLE `topic_entry` (
`entry_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` bigint(20) unsigned NOT NULL,
`entry_created` datetime NOT NULL,
`entry_object` text,
`level` tinyint(4) NOT NULL DEFAULT '3',
PRIMARY KEY (`entry_id`),
KEY `entry_created` (`entry_created`),
KEY `level` (`level`),
KEY `topic_id_2` (`topic_id`,`entry_id`)
) ENGINE=MyISAM;

CREATE TABLE `topic_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`topic_id` bigint(20) unsigned NOT NULL,
`user_id` varchar(100) NOT NULL,
`private` enum('y','n') DEFAULT NULL,
`freq` enum('a','d','w') DEFAULT NULL,
`topic_id_freq` varchar(10) DEFAULT NULL,
`casematch` enum('0','1') DEFAULT '0',
`textmatch` enum('0','1') DEFAULT '0',
`topic_name_case` varchar(100) DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `topic_id_user_id` (`topic_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `topic_id_freq` (`topic_id_freq`)
) ENGINE=MyISAM;
This is the query that i am trying to run

explain
select te.topic_id,te.entry_id
from topic_entry te
WHERE te.topic_id in (select topic_id
from topic_user where user_id ='xyz')
AND te.level=4

ORDER BY te.entry_id DESC
LIMIT 5;


The explain output shows that it is scanning all rows
| 1 | PRIMARY| te | range | PRIMARY,level | PRIMARY | 8 | NULL| **722978** | Using where |
| 2 | DEPENDENT SUBQUERY | topic_user | unique_subquery | topic_id_user_id,user_id | topic_id_user_id | 310 | func,const | 1 | Using index; Using where |

If i remove order by clause from query it work perfectly
Any pointers would be appreciated

Options: ReplyQuote


Subject
Views
Written By
Posted
query scanning all rows
2488
September 12, 2012 01:20PM
1465
September 15, 2012 12:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.