MySQL Bugs: #53553: "Copying to tmp table" spend a lot time

Bug #53553	"Copying to tmp table" spend a lot time
Submitted:	11 May 2010 7:14	Modified:	11 Jun 2010 14:10
Reporter:	Minfeng Zhang	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.46	OS:	Windows (Windows 7 x64)
Assigned to:		CPU Architecture:	Any

Description:
May be it's a bug in MySQL 5.x
I tried to migrate a database from mysql 4.0 to mysql 5.1 this week, and the data seems fine.
But when I query from the new server MySQL 5.1.46, some statements are very slow. Then I test it in phpmyadmin v3, found "Copying to tmp table" spend a lot time:

status time
================
starting 0.000005 
checking query cache for query 0.000060 
Opening tables 0.000008 
System lock 0.000007 
Table lock 0.000028 
init 0.000033 
optimizing 0.000011 
statistics 0.000018 
preparing 0.000009 
Creating tmp table 0.003042 
executing 0.000001 
Copying to tmp table 4.877600 
Sorting result 0.001990 
Sending data 0.000451 
end 0.000002 
removing tmp table 0.002626 
end 0.000009 
query end 0.000003 
freeing items 0.000091 
storing result in query cache 0.000021 
logging slow query 0.000001 
cleaning up 0.000006 

and if I do it in mysql 4.0, it's very fast to return the result. ( in 0.0003 second );
increasing "tmp_table_size" and "max_heap_table_size" to 256M does'nt help. 

How to repeat:
Two tables: (`flows` and `fnodes` both have about 300000 records)
=====================================
CREATE TABLE IF NOT EXISTS `flows` (
  `fid` int(10) unsigned NOT NULL auto_increment,
  `fname` tinytext NOT NULL,
  `fstarter` smallint(5) unsigned NOT NULL default '0',
  `fstime` datetime NOT NULL default '0000-00-00 00:00:00',
  `fedays` smallint(5) unsigned NOT NULL default '0',
  `fsteps` tinyint(3) unsigned NOT NULL default '0',
  `fstatus` tinyint(3) unsigned NOT NULL default '0',
  `ftype` varchar(20) NOT NULL default '',
  `fcjob` tinyint(3) unsigned NOT NULL default '0',
  `ftext` text NOT NULL,
  `fatt` text NOT NULL,
  PRIMARY KEY  (`fid`),
  KEY `fstatus` (`fstatus`),
  KEY `fstime` (`fstime`),
  KEY `fstarter` (`fstarter`),
  KEY `fcjob` (`fcjob`)
) TYPE=MyISAM AUTO_INCREMENT=325766 ;

-- --------------------------------------------------------

--
-- 表的结构 `fnodes`
--

CREATE TABLE IF NOT EXISTS `fnodes` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `fid` int(10) unsigned NOT NULL default '0',
  `nid` tinyint(3) unsigned NOT NULL default '0',
  `nname` varchar(50) NOT NULL default '',
  `nmembers` text NOT NULL,
  `nmleft` tinyint(3) unsigned NOT NULL default '0',
  `nmemleft` text NOT NULL,
  `nall` char(1) NOT NULL default '',
  `nactype` char(1) NOT NULL default '',
  `msgalert` char(1) NOT NULL default '',
  `forward` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `fid` (`fid`),
  KEY `nid` (`nid`),
  KEY `nmleft` (`nmleft`)
) TYPE=MyISAM AUTO_INCREMENT=324478 ;

===========================
the query:

SELECT SQL_CALC_FOUND_ROWS DISTINCT flows.fid, fname, fstarter, fstime, fedays, fstatus, fsteps, ftype, fcjob, fatt
FROM flows
INNER JOIN fnodes ON ( fnodes.nid <= flows.fstatus +1
AND fnodes.fid = flows.fid )
WHERE fnodes.nmembers LIKE '%,309,%'
AND fnodes.nmemleft NOT LIKE '%,309,%'
ORDER BY flows.fstime DESC

Please, send the results of EXPLAIN for the problematic query.

EXPLAIN result:
1 SIMPLE flows ALL PRIMARY NULL NULL NULL 315800 Using temporary; Using filesort 
1 SIMPLE fnodes ref fid,nid fid 4 myoagov.flows.fid 1 Using where; Distinct

EXPLAIN result (using PHP4.4.9+MySQL4.0. it's very fast in MySQL4.0)
===================================================================
fnodes ALL NULL NULL NULL NULL 321792 Using where; Using temporary; Using filesort 
flows eq_ref PRIMARY PRIMARY 4 fnodes.fid 1 Using where

MySQL 5.1.48
============
mysql> explain SELECT SQL_CALC_FOUND_ROWS DISTINCT flows.fid, fname, fstarter, fstime, fedays, fstatus, fsteps, ftype, fcjob, fatt FROM flows INNER JO
IN fnodes ON ( fnodes.nid <= flows.fstatus +1 AND fnodes.fid = flows.fid ) WHERE fnodes.nmembers LIKE '%,309,%' AND fnodes.nmemleft NOT LIKE '%,309,%' ORDER BY flows.fstime DESC ;
+----+-------------+--------+------+---------------+------+---------+-------------------+--------+---------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref               | rows   | Extra                           |
+----+-------------+--------+------+---------------+------+---------+-------------------+--------+---------------------------------+
|  1 | SIMPLE      | flows  | ALL  | PRIMARY       | NULL | NULL    | NULL              | 315800 | Using temporary; Using filesort |
|  1 | SIMPLE      | fnodes | ref  | fid,nid       | fid  | 4       | myoagov.flows.fid |      1 | Using where; Distinct           |
+----+-------------+--------+------+---------------+------+---------+-------------------+--------+---------------------------------+

after deleting the index 'fid', 'nid' in table `fnodes`, the query now is fast.
so maybe I didn't set index properly, or the way mysql operate on "join" is not so good?

Having all indexes back in place, had ou tried to run ANALYZE for both table in 5.1.x before EXPLAIN? I wonder if wrong/outdated statistics can be the reason of this problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".