You say you adjusted the max_seeks_for_key, but have you adjusted the sort_buffer? Sorting does seem to be your bottleneck and that would be a very important setting to change.
Aside from that, post the explain from your query so we can see what MySQL is doing.
On Dec 28, 2004, at 11:53 PM, Adam Randall wrote:
Hopefully someone can help me out here :)
I have a pretty simple table that is 400k rows and growing. I'm doing, to me, some relatively simple searches but they are taking forever (6 seconds in my case is faaaaaaar too long). Anyway, here's the setup:
Tested on MySQL 4.0.21 (Windows 2000 Server, single 1.5GHz Athalon) and 4.1.7 (Mac OS X dual 2.0GHz G5):
Schema of table (there's more than this, but this is the focus of my current issue):
create table queries ( id bigint unsigned primary key auto_increment, created datetime not null, author varchar(100) not null, index (created) index (author) )type=myisam;
so, when I do something like this:
select created, author from queries where created >= '2004-01-01' and created < '2005-01-01' limit 10;
Or
select created, author from queries order by author limit 10;
I get back what I want in less than 1 second. If I do this, though:
select created, author from queries where created >= '2004-01-01' and created < '2005-01-01' order by author limit 10;
It takes between 4 and 10 seconds (depending on what I'm up to). It seems like the where and the order don't play nice, and I can't seem to figure out how to get an index that applies to all the criteria. I've tried multicolumn indexes, forcing indexes, setting the max_seeks_for_key to 100, etc. Nothing seems to make it better.
If anyone has any advice, I'd love to hear it.
Adam.
-- ----------------------------------------------------------------------- Adam Randall http://www.xaren.net [EMAIL PROTECTED] AIM/iChat: blitz574 [EMAIL PROTECTED]
"Macintosh users are a special case. They care passionately about the Mac OS and would rewire their own bodies to run on Mac OS X if such a thing were possible." -- Peter H. Lewis
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
