SQL Tuning

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]

Ok, are you sure it’s the “<>” that’s causing your problems? In your tables (log_count and log_count_arch) you have an index on “uid” but your best/most limiting filter (“-”)is on the “ip” column which has no index. It appears that these tables store the same information. What is the difference?

In your query:

EXPLAIN SELECT uid, ip FROM log_count WHERE ip = ‘1851220710’ AND uid <> '4796596’UNIONSELECT uid, ip FROM log_count_arch WHERE ip = ‘1851220710’ AND uid <> ‘4796596’ GROUP BY uid;

I believe this statement will not do anything useful. What are the expected results? Why does the second part after the UNION have a “GROUP BY” clause? As I understand it… the first half gets a list of all (current?) uids (not distinct and not filtered) for a single ip and the second half gets a (distinct and not filtered) list of uids for a single ip and then filters out any duplicates (both columns) from the first part (UNION does this).

You’re basically requiring 2 full table scans (that second table has over 30 million records) to weed out a relatively small number of records. Would it make more sense to get a list of matching records for the uid and then compare to whatever you’re comparing (assuming you’re looking for an unused uid/ip combination).

The first table definition in the second query is missing something. I have an idea what you’re trying to do but I’m not tackling that one. There are some great ways to do full text searches and I don’t think this is one of them.

Troy