Slow MySQL queries

software development

#1

Hello. I have a MySQL database with approximately 36,000,000 records loaded into a single MyISAM table. I wouldn’t expect a search of such a large dataset to be instantaneous, but as it stands a simple SELECT query takes around 2 minutes to process. Is there any way to speed this up? I know a fair amount about database topography, but I can’t see a simpler means of storing the data.

A friend suggested loading the entire table into memory, but I doubt Dreamhost would allow such a practice on a shared server. Would switching to InnoDB make a difference? Thank you.


#2

I suppose that size of data store would be a bit too big for keeping in memory and I am not sure if the memory engine is available. It might be an idea to try InnoDB as that is supposed to work well with very large data stores. I think Google use it.

Maybe you could set up a test database. I think you would use ALTER to convert the tables to the new engine?

Is that database your address book? :slight_smile:


Norm

Opinions are my own views, not DreamHosts’.
I am NOT a DreamHost employee OK!! :@

You act on my advice at your own risk!


#3

Close! A couple weeks ago, Slashdot reported on a gaffe on AOL’s part where the search data for 650,000 members was released (http://yro.slashdot.org/article.pl?sid=06/08/07/2022244). I’m taking a data mining course next semester, so I thought dropping it in a database and making a web interface would be good practice.

Maybe AOL will release the address books of those users next :wink: