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:
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]
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]
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)
[\code]
Query 2:
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:
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]