MySQL Forums
Forum List  »  Optimizer & Parser

Query Tuning for NOT EQUAL TO <> operator
Posted by: Irfan Ali
Date: October 22, 2010 07:42AM

We are facing huge problem with <> operator queries because log_count_arch contains more than 30 million rows and we have to extract all id's accept the one not meeting criteria (<>).

what's the best query execution path to satisfy <> queries and re-write query option and using temporary and using filesort operations.

mysql> show create table log_count_arch\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `log_count_arch` (
`uid` int(11) NOT NULL DEFAULT '0',
`lasttime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`ip` int(11) NOT NULL DEFAULT '0',
KEY `arch_uid_idx` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table log_count\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `log_count` (
`uid` int(11) NOT NULL DEFAULT '0',
`lasttime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`ip` int(11) NOT NULL DEFAULT '0',
KEY `uid_idx` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.56 sec)


EXPLAIN SELECT uid, ip FROM log_count WHERE ip = '1851220710' AND uid <> '4796596'

UNION

SELECT uid, ip FROM log_count_arch WHERE ip = '1851220710' AND uid <> '4796596' GROUP BY uid;

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: log_count
type: ALL
possible_keys: uid_idx
key: NULL
key_len: NULL
ref: NULL
rows: 46
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: UNION
table: log_count_arch
type: ALL
possible_keys: arch_uid_idx
key: NULL
key_len: NULL
ref: NULL
rows: 30276618
Extra: Using where; Using temporary; Using filesort
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
3 rows in set (0.00 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
Query Tuning for NOT EQUAL TO <> operator
10639
October 22, 2010 07:42AM


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.