List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:December 29 2004 2:14pm
Subject:Re: Help: Index performance on moderately sized table
View as plain text  
Well, obviously I would try creating compound indexes. So one index 
would be created, author and the other would be author, created. But it 
seems like you tried that.

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
> randalla@stripped                                   AIM/iChat:  blitz574
> webtech@stripped
>
> "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/mysql?unsub=1
>
>
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

Thread
Help: Index performance on moderately sized tableAdam Randall29 Dec
  • Re: Help: Index performance on moderately sized tableBrent Baisley29 Dec