MySQL Forums
Forum List  »  Optimizer & Parser

'not in' query is very slow how to change it?
Posted by: kevin smith
Date: November 15, 2010 09:01AM

query:
mysql> explain select `question` from questions where `iid` = '2' and `id` >= '14' and `question` not in (select q from questions,occur where q = question and occurid = 'i25085126' and occur.a <> '' group by q order by occur.id desc) and `active` = 1 order by ord asc limit 0, 1;
+----+--------------------+-----------+-------+---------------+--------------+---------+-------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+-------+---------------+--------------+---------+-------------------+--------+----------------------------------------------+
| 1 | PRIMARY | questions | range | PRIMARY | PRIMARY | 4 | NULL | 48 | Using where; Using filesort |
| 2 | DEPENDENT SUBQUERY | occur | ALL | NULL | NULL | NULL | NULL | 129015 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | questions | ref | idx_question | idx_question | 153 | RiskStomp.occur.q | 5 | Using where; Using index |
+----+--------------------+-----------+-------+---------------+--------------+---------+-------------------+--------+----------------------------------------------+
3 rows in set (0.00 sec)

Execution time: 13 sec

CREATE TABLE `questions` (
`id` int(11) NOT NULL auto_increment,
`iid` int(11) default NULL,
`question` varchar(150) default NULL,
`dt` timestamp NOT NULL default CURRENT_TIMESTAMP,
`active` varchar(2) default '1',
`ord` varchar(3) default NULL,
`rule` text,
`descp` text,
PRIMARY KEY (`id`),
KEY `idx_question` (`question`)
) ENGINE=MyISAM

CREATE TABLE `occur` (
`id` int(10) NOT NULL auto_increment,
`dt` timestamp NOT NULL default CURRENT_TIMESTAMP,
`occurid` varchar(50) NOT NULL,
`qid` varchar(10) NOT NULL,
`aid` varchar(10) NOT NULL,
`q` text,
`a` text,
`user` varchar(10) default NULL,
`ip` varchar(20) default NULL,
`raw` varchar(2) default '0',
PRIMARY KEY (`id`),
KEY `idx_occur` (`qid`)
) ENGINE=MyISAM

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
'not in' query is very slow how to change it?
6395
November 15, 2010 09:01AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.