LARGE Mysql Tables

[zhamm]

Does anyone have any experience with extremely large mysql database tables? I have several database tables imported from Informix which are over 8 million rows, and even with indexes and reconfiguring mysql to use more memory, queries are still very slow.

Any suggestions?

TIA

Zack

[[deleted]]

What kind of queries, what kind of system, wat kind of data, and how slow is slow?

[Anon]

Complex joins against various tables, Text data, RH Linux 7.1 using Mysql 3.23 and slow means 30+ minutes to run a query. I've read the Mysql docs on some optimization tricks, but so far none have seemed to work.

vincent wrote:

What kind of queries, what kind of system, wat kind of data, and how slow is slow?

[trellie]

well... mysql isn't really designed to handle such huge databases....
I don't think you can speed it up other then using a faster server (more mem, faster CPU, faster HD)...

[[deleted]]

You could try looking at your queries to see just how intelligent they are.
You might be joining two tables and forgetting to specify just what they should be joined on.

Also, make sure your machine has enough memory so it never ever has to use the swapspace. memory has accesstimes of 10ns, the harddisk accesstime is one thousand times greater. Simply put, that means a 1 second query could take 20 minutes just because of swapspace :-)