MySQL Forums
Forum List  »  Optimizer & Parser

Slow Queries : Using Temporary , Using Filesort
Posted by: Irfan Ali
Date: October 22, 2010 06:41AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Queries : Using Temporary , Using Filesort
7030
October 22, 2010 06:41AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.