Query Tuning for NOT EQUAL TO <> operator

[Irfan Ali]

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)