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.
For query 2 and query 3 I tried with Fulltext index as well but no luck. I also tried covering index but again no luck.

Can someone please suggest how to improve especially get rid of using temporary and filesort.

Query 1:

[code]
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)
[\code]
[code]
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)
[\code]
[code]
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:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
3 rows in set (0.00 sec)
[\code]


Query 2:


[code]
mysql> EXPLAIN select SQL_CALC_FOUND_ROWS id, keyword from keyword
where keyword like 'p%'
AND count IN (2,3,4)
ORDER BY rate DESC, time DESC LIMIT 88800,6;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: keyword
type: range
possible_keys: keyword_idx,count,keyword_ft
key: keyword_idx
key_len: 257
ref: NULL
rows: 330510
Extra: Using where; Using filesort
1 row in set (0.00 sec)


mysql> show create table keyword\G
`keyword` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '',
`seloff` int(10) unsigned NOT NULL DEFAULT '0',
`seloff` int(10) unsigned NOT NULL DEFAULT '0',
`buyoff` int(10) unsigned NOT NULL DEFAULT '0',
`rate` int(9) NOT NULL DEFAULT '0',
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`count` tinyint(4) DEFAULT '0'

PRIMARY KEY (`sb_id`),
KEY `keywords_idx` (`keyword`),
KEY `seloff` (`seloff`),
KEY `count` (`count`),
FULLTEXT KEY `keyword` (`keyword_ft`)
[\code]

Query 3:


[code]
mysql> show create table uniqueproducts\G
*************************** 1. row ***************************
CREATE TABLE uniqueproducts
id` int(11) NOT NULL AUTO_INCREMENT,
title` varchar(255) DEFAULT '',
keyword` varchar(255) DEFAULT NULL,
comp` varchar(255) NOT NULL DEFAULT '',
date` int(11) DEFAULT NULL;

PRIMARY KEY (`id`),
KEY `uid` (`uid`),
KEY `date_idx` (`date`),

mysql> show create table mem_pack\G
*************************** 1. row ***************************
CREATE TABLE mem_pack
id` int(11) unsigned NOT NULL AUTO_INCREMENT,
uid` int(11) DEFAULT NULL,
type` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),
UNIQUE KEY `uid` (`uid`),
KEY `type_idx` (`type`)


mysql>EXPLAIN SELECT o.id, o.title, o.desc, o.uid, o.keyword, o.comp FROM uniqueproducts o
INNER JOIN mem_pack m ON o.uid = m.uid
WHERE o.approv = 1
AND m.type = 3
AND match(o.title , o.keyword ) against ('+shirt +and +trouser/jeans' in boolean mode)
GROUP BY o.uid
ORDER BY o.date ASC, o.id ASC
LIMIT 0, 10;

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: o
type: index
possible_keys: uid
key: uid
key_len: 5
ref: NULL
rows: 2
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: m
type: ref
possible_keys: uid,type_idx
key: uid
key_len: 5
ref: test.o.uid
rows: 11
Extra: Using where
2 rows in set (0.00 sec)
[\code]