Matthias, Can you send us your table index definitions and the output of an EXPLAIN command on your query?
ie DESCRIBE pool; SHOW INDEX FROM pool; EXPLAIN SELECT sendnr FROM pool where sendnr = 111073101180; I'm pretty sure we can improve this - I've got a table with 55 million records (though only 3 columns) and a search like the one you've got takes 0.07 seconds on a box similar to your dev box. Andy > -----Original Message----- > From: Schonder, Matthias [mailto:[EMAIL PROTECTED] > Sent: 18 September 2003 10:25 > To: '[EMAIL PROTECTED]' > Subject: Performance Problems > > > Hei :) > > I have an extreme performance problem with a MySQL-DB. > The database consists of 21 tables where all except three are > storing only a > few records. Two have about 150.000 records but they are only used > temporary. The main table is rather huge, it has 90 columns and now after > three month it has 500.000 records... but in the end it has to > store data of > 36 month. > But since the table has grown to over 350.000 records I ran into massive > performance problems. Querying for one record (Example: SELECT sendnr FROM > pool where sendnr = 111073101180) takes 8 seconds via command line! > The table is indexed and i put the my-huge.cnf into /etc/ as my.cnf > > How can I get more performance out of the mysql? > The server which currently only hosts this database and is > running apache1.3 > with php4 for providing results via intranet. > The OS is FreeBSD 5.1. > We are running two servers with the same enviroment > One is a DualXeon 2.4ghz (with HT), 3GB RAM, 105GB HD (RAID5) > the other one is a P4 with 2.4ghz, 1GB RAM and 120GB HD > > The Dual is the Productionserver, the P4 the developement- and > test server. > The querey takes that long on BOTH machines so it seems clear the > DB itself > is causing the performance problem. > > So anyone can help? This is really urgend and will save my life :) > > Big thanks in advance. > > Pacem, > > Matthias Schonder > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
