How can I optimize my mySQL database? It is huge (over 200K items) and it runs SUPER slow. Please help!?

Question

We are using Debian with PHP. The site is similar to... http://www.qwikfliks.com/xgenesis.asp?nwct=yes, in which there are THOUSANDS of items that customers can browse through. The machine is an:
2800+ AMD Athlon64
1GB PC3200 Dual Channel RAM
80GB HD

Answer 1

mySQL is as fast as it gets. But it could be your table design.



Break the tables down to the minimum, then JOIN using indexes. So why carry a full address, when you can look that up from that single record. That's what relational databases and SQL are all about.



But from my point of view, your website is quite fast. GOOD WORK dude.



Good luck and Happy Computing!

Answer 2

Make sure that you have the tables indexed on the keys that you are using to access the data for selects and joins. It will help speed things up a bit. Also check your systems load to make sure that there isn't something else running that is sucking up the resources.

Answer 3

This link has all the information you need. I would place emphasis on indexes and how your code accesses the database. See notes on the explain command.

Answer 4

More Ram and faster HD is the biggest Hardware change you can do. 200k is not that bad, your queries and schema may be bad and need to be rewritten.