Slow Queries : Using Temporary , Using Filesort

[Irfan Ali]

We have couple of queries running badly because of temporary table creation and filesort operation. 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> 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`)


Query 2:


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)