The first time I run this query on a page load, it always takes about 100ms, even though it should take about 1. I've ran the exact same query through the MySQL console and via PhpMyAdmin, and it's always fast.
I have timed it inside PHP like so:
$t = microtime(true);
mysql_query($sql, $this->id);
die((microtime(true)-$t)*1000);
If I run that exact same query a 2nd time (say by duplicating the middle line), the 2nd time runs almost instantly.
The query looks like this:
SELECT `user_id`, `login`, `first_name`, `last_name`, `name`, `email`, ... FROM `users` WHERE `user_id`='1000' LIMIT 1
Through experimentation I discovered that if I reduce the number of fields I select, it suddenly runs fast. It jumps from 1ms to 100ms when I add a 26th column. It doesn't seem to matter what the 26th column is, even if it's a constant like "1", it suddenly becomes slow.
What could be causing this, and how can I fix it?
The schema:
CREATE TABLE `user` (
`f1` int(11) unsigned NOT NULL DEFAULT '0',
`f2` varchar(50) NOT NULL DEFAULT '',
`f3` varchar(32) NOT NULL DEFAULT '',
`f4` varchar(50) DEFAULT NULL,
`f5` varchar(50) DEFAULT NULL,
`f6` varchar(100) NOT NULL DEFAULT 'Anonymous',
`f7` varchar(100) DEFAULT NULL,
`f8` varchar(100) NOT NULL DEFAULT 'Anonymous',
`f9` tinyint(1) NOT NULL DEFAULT '0',
`f10` tinyint(1) unsigned NOT NULL DEFAULT '0',
`f11` varchar(250) DEFAULT NULL,
`f12` int(10) unsigned NOT NULL DEFAULT '0',
`f13` varchar(250) NOT NULL DEFAULT '',
`f14` int(10) unsigned NOT NULL DEFAULT '0',
`f15` varchar(100) DEFAULT NULL,
`f16` varchar(25) DEFAULT NULL,
`f17` varchar(25) DEFAULT NULL,
`f18` varchar(25) DEFAULT NULL,
`f19` varchar(200) DEFAULT NULL,
`f20` varchar(50) DEFAULT NULL,
`f21` varchar(50) DEFAULT 'BC',
`f22` varchar(50) DEFAULT 'Canada',
`f23` varchar(20) DEFAULT NULL,
`f24` bigint(20) unsigned NOT NULL DEFAULT '0',
`f25` bigint(20) unsigned NOT NULL DEFAULT '0',
`f26` int(5) unsigned zerofill NOT NULL DEFAULT '00000',
`f27` tinyint(1) NOT NULL DEFAULT '0',
`f28` tinyint(1) unsigned NOT NULL DEFAULT '0',
`f29` tinyint(1) NOT NULL DEFAULT '0',
`f30` tinyint(1) unsigned NOT NULL DEFAULT '0',
`f31` varchar(50) DEFAULT NULL,
`f32` varchar(100) NOT NULL DEFAULT '',
`f33` bigint(20) unsigned NOT NULL DEFAULT '0',
`f34` bigint(20) unsigned NOT NULL DEFAULT '0',
`f35` varchar(250) DEFAULT NULL,
`f36` text NOT NULL,
`f37` tinyint(1) unsigned NOT NULL DEFAULT '0',
`f38` bigint(20) NOT NULL DEFAULT '0',
`f39` bigint(20) NOT NULL DEFAULT '0',
`f40` tinyint(1) unsigned NOT NULL DEFAULT '0',
`f41` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`f1`),
UNIQUE KEY `f2` (`f2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I've changed the field names to protect the innocent.
Update: I may have been mistaken about the magic column limit. I think it is a byte limit after all... I tried with all numbers, except for the handful of fields I actually need:
SELECT `f1`, `f2`, `f3`, `f4`, `f5`, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55 FROM `wx_user` WHERE `user_id`='1000' LIMIT 1
That runs in 1ms. If I add "56" to the end, it takes ~100ms.
I've done some more testing:
mysql_query("SELECT 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' FROM `user` WHERE `f1`='1000' LIMIT 1");
Somewhere between 1200 and 1300 a's it jumps from 1ms to 100ms.
I can repeat the same thing using more columns, but smaller data (ints).
This suggests to me two things:
Now that I think about it, this number is very close to the number John suggested. The 1300 chars + overhead is probably equal to that 1500 MTU limit John mentioned.
Is that a MySQL setting, or OS setting? Something I can experiment with?
MySQl caches your query, so the second time you execute it, it loads from cache and will be much faster.
Also be sure to have the right indexes on your tables, as this can speed up things a lot.
As John stated second run of the same query retrieves its result from cache so it's almost instant.
That being said there are some cases in which query might 'run' longer than it should when Xth column is being added. Few possibilities:
You can also use builtin MySQL query profiling to find out which query execution stage takes so much time. Run:
SET profiling = 1;
[..run Your queries, each one is numbered..]
SHOW PROFILE FOR QUERY n; -- SHOW PROFILE FOR QUERY 1;
SET profiling = 0;
You should have pretty good insight where additional time is spent. Read more about query profiling here: MySQL Query Profiling
Well, it's all true that MySQL has a cache and it speeds things up......
BUT there's more than just that. The problem is not in your query. I encounter the same thing, and the reason I can't debug it is exactly the cache: I can't produce the same problem again. It's not just MySQL, it's also the filesystem that caches things so it's completely impossible to reproduce the initial slowness - plus I don't know for sure if it could happen at all for the second time, maybe the reasons behind cause it not to happen again. Tried to restart MySQL, tried to flush the cache, but nothing can reproduce the first run.
And what's even more mysterious! There's a workaround that makes no sense: if you do a CHECK TABLE before the query (and make sure it's only the first time!), the query itself will run at normal speed. No, not the same speed as it does when cached already; it's a normal-but-still-first-time speed. Calling it again results instantly, as you'd expect.
Now everyone nods and says "of course, the filesystem caches the table file when doing the CHECK so next time it runs faster". But that's not the case. If I try to read the MYD file into memory with any other program (I did a simple "copy"), it doesn't solve the problem, not even if I copy the indices as well. CHECK, however, seems to do something that really helps.
Conclusion: - this is not a simple "slow query" issue, this is dark sorcery. And obviously, this workaround is not for a production environment. I just mention it because it shows two important things:
Hope this helps a few ppl out there - took me some days :)