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]
