I used to have another user for dbforums but it has been lost like so many pairs of socks. For the life of me I cannot figure out what is going on with this...
When I run the query on my test Windows MySQL 5.0.45 server it runs fast (< 1 second) and when I run it on my production Linux MySQL 5.1 server it runs slow (~20 seconds). Both my.cnf are similar but the problem could be in here. The articles table contains a modest 45000 rows and each article has 1 category. There are 15 categories. If an article leaves the train station traveling westbound at 45 MPH how long will it take for me to go crazy...
My production server had 5.0.27 on it with the same problems which is why I upgraded to 5.1, to get the profiler. It was at this point I found out 5.1 does't have the profiler yet becase of blah blah blah... yay for me. When the query runs the bulk of the time seems to be in the "sending data" phase when I do a show full processlist;. Does anyone have any ideas what might be going on? I spent the last couple days on this and it is delaying the launch of my project.... ugh. Enough to make me start smoking again.
Here is my schema:
Code:
CREATE TABLE `articles` (
`article_id` int(7) unsigned NOT NULL auto_increment,
`views` int(6) unsigned NOT NULL default '0',
`title` varchar(200) NOT NULL,
`author` varchar(35) NOT NULL,
`text` mediumtext NOT NULL,
`created` datetime NOT NULL default '2020-07-27 12:00:00',
PRIMARY KEY USING BTREE (`article_id`),
KEY `author` USING BTREE (`author`,`created`),
KEY `views` USING BTREE (`views`,`created`),
KEY `created` USING BTREE (`created`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
CREATE TABLE `article_cats` (
`category_id` smallint(4) unsigned NOT NULL,
`article_id` int(7) unsigned NOT NULL,
PRIMARY KEY (`category_id`,`article_id`),
UNIQUE KEY `pk_rev` USING BTREE (`article_id`,`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `categories` (
`category_id` smallint(4) unsigned NOT NULL auto_increment,
`parent` smallint(4) unsigned default NULL,
`name` varchar(30) NOT NULL,
PRIMARY KEY (`category_id`),
KEY `parent` (`parent`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
I have tried many variations of this query. All of which run great on my windows test server and slow on the production.
Code:
select a.*
from articles a
inner join article_cats ac
on ac.article_id = a.article_id and ac.category_id = 1
where `created` between '2007-12-18 21:28:46' and '2007-12-20 21:28:46'
order by `created` desc
limit 0,10
And this is what explain tells me (deleted ID, key_len and ref to conserve space)...
Code:
+-------------+-------+--------+-----------------+---------+------+-------------+
| select_type | table | type | possible_keys | key | rows | Extra |
+-------------+-------+--------+-----------------+---------+------+-------------+
| SIMPLE | a | range | PRIMARY,created | created | 6516 | Using where |
| SIMPLE | ac | eq_ref | PRIMARY,pk_rev | PRIMARY | 1 | Using index |
+-------------+-------+--------+-----------------+---------+------+-------------+
If you have read this far, I owe you a beer.
dBforums
>
Query slow on production but fast on test server...
12-21-07, 01:35
Linear Mode
