MySQL Forums
Forum List  »  Performance

VARCHAR performance
Posted by: Andrew Ling
Date: March 06, 2014 05:15AM

I have a table that looks like...

mysql> show create table tags;
+-------+------------------------------------------------------------
| Table | Create Table
+-------+------------------------------------------------------------
| tags | CREATE TABLE `tags` (
`rushID` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`tagtype` varchar(255) NOT NULL,
`start` int(11) NOT NULL,
`finish` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
KEY `rushid` (`rushID`),
KEY `start` (`start`),
KEY `finish` (`finish`),
KEY `tagtype` (`tagtype`(32)),
KEY `info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

I do a search on this table that looks like

SELECT DISTINCT start, finish, info FROM tags
WHERE tagtype = "Q_PixelAspect" AND
rushid = "418bbafefd8f00506887f858b8160987" AND
start < 139 AND (finish > 0 || finish = 0) ORDER BY start ;

Doing a describe on this I get

id: 1
select_type: SIMPLE
table: tags
type: ref
possible_keys: rushid,start,finish,tagtype
key: rushid
key_len: 96
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort

So it uses a temporary. Often this search goes very slow. Investigating shows that it is always creating a temporary table on disk. I've got max_heap_table_size and tmp_table_size set to 16M, which should be ample. I have tried increasing these and it makes no difference.

But, if I change the info column so it is VARCHAR(170) it stops creating temporary tables on disk and the query is always much faster. Changing the tagtype VARCHAR size doesn't seem to make any difference.

I can't find any reference to this magic 170 number and all the discussions on performance suggest VARCHARs can be upto 64K and still not write to disk. So what is going on? What do I have to do to use a VARCHAR bigger than 170 and still avoid temporary tables going to disk.

I have tried this on versions 5.1.47 (32 bit) & 5.5.16 (64 bit) all running on Windows with the same results.

Any help would be much appreciated.

Options: ReplyQuote


Subject
Views
Written By
Posted
VARCHAR performance
1952
March 06, 2014 05:15AM
903
March 07, 2014 08:48AM
874
March 07, 2014 10:01AM
844
March 07, 2014 10:13AM
750
March 07, 2014 10:57AM
969
March 08, 2014 12:53PM


Sorry, you can't reply to this topic. It has been closed.

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.