On Thu, 19 Oct 2006 23:19:35 +0800, in mailing.database.mysql -
<no****@home.com>
<45********@news.starhub.net.sgwrote:
>|
| Use Explain [your query goes here]
| then look at the results. Which table(s) are accessed most frequently
| during the queries execution.
| Can some of the fields be indexed.
| Are the joins (if any) optimised.
| How much data are you bringing through (specifying 20 fields but only
| displaying 2).
| ---------------------------------------------------------------
| jn******@yourpantsyahoo.com.au : Remove your pants to reply
| ---------------------------------------------------------------
|
|
| My query is a very simple one.
|
| SELECT id FROM foobar WHERE name = 'bla bla' LIMIT X,Y;
|
| Where X,Y are constants.
|
| There are only two fields, a primary key and an indexed field.
Well you've got me.
A couple of things though.
You don't say what OS you're using. Shutdown your mySQL server and
defrag your system. Maybe the files are all over the place and mySQL
has to do extra work to retrieve the data.
Run your query again - any noticable difference
What table type are you using myISAM or INNODB?
You could try create a copy of your table (minus the PK and index)
then import the data from the orginal table to the duplicate. Then add
your PK and index. I'm thinking that the indices maybe in need of a
freshen up. Try your query on the duplicate table, is there any speed
difference? Also my adding the PK and index fields later this will
give you an indication of the time required for mySQL to rebuild the
data - handy for when you drop pk and drop index/create pk/index.
What else is running on your system? Is there a lot of disk thrashing
when you are doing the slow query. Maybe the system is running out of
resources and needs to do a lot of memory swapping.
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------