MySQL Forums
Forum List  »  General

sorting tasks taking too long
Posted by: Greg Collier
Date: January 21, 2005 05:37PM

We feel that our mysql database is slower than it is
justified by its size.

For example our perl script uses the following request
to retrieve the last 10 ads submitted to 'Atlanta
employment' section:

select * from tadsemployme where city='Atlanta' order
by idnum desc limit 20

Note that idnum is a primary autoincrement key, there
is also a city-idnum combined index, so that this
search should be quick. It is not.

I tried "select idnum ..." instead of "select * ..."
to decrease the size of retrieved (and subsequently
sorted) data, but it did not help. I tried to increase
the mysql server memory dedicated to keeping indices
and sorting. Did not help much either.

Any insight into why is it like this? If you can
optimize the database or suggest a better way to
extract the records in the 'latest first' fashion that
will be great.

Another type of search that we use extensively and
have trouble with is

select * from tadsemployme where co='Boehringer
Ingelheim' and city='Atlanta' order by idnum desc
limit 20

I expect it to consume less memory, because there are
fewer ads that satisfy criteria "co='Boehringer
Ingelheim' and city='Atlanta'". This one is slow too,
despite the appropriate index is available.

What I also have trouble understanding is that these
requests are sometimes quicker than the other times
(usually the first request is 10 times slower than the
subsequent ones), and I am not sure that caching or
server load explains the difference.

We have upgraded to mysql 4.0 but little difference was noticed. Any thoughts?

Options: ReplyQuote


Subject
Written By
Posted
sorting tasks taking too long
January 21, 2005 05:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.