Mysql Local server hardware

In anticipation of a live scenario, I have been building my entire site on a Thinkpad 390x which has a 500MHz processor with a whopping 256MB of 100MHz RAM.

I have loaded my database with 200k+ rows of garbage data, trying to test my queries. I've noticed

a huge performance hit because of this obviously. All of my queries are using indices, however some contain up to 15+ join (with all using indices).

MySQL has 16MB of cache enabled for use, which brings down query times to next to nothing, but at what consequences? With a dynamic website, I can't see how a cache could help a whole lot when I allow unique queries such as a search feature. I am also using eaccelerator for the PHP.

The 10+ second queries happen when I use COUNT(*) AS total with multiple JOIN and WHERE conditions. The other queries which only retrieve a single row or two perform at the same level as when I only had a few rows. I also noticed开发者_Python百科 that mysqld hits 99.7% of CPU strain during a query almost immediately.

Essentially I don't know if the software is at fault or more so of the hardware. The laptop is ancient, but at the same time it has only ONE load to worry about without other users stressing the database.

Thanks

EDIT Explain statement on a trouble child that takes ~8 secs: Here is more info on table setup

It's very hard to know without fully understanding the table structure, data and queries, but what you're describing seems like reasonable response times for the queries that you're running. Here's some thoughts:

• Consider reducing the query size. Only request what you need at the time, not everything you might need eventually.
• Frequently, a couple of small queries will out-perform one huge one
• Depending on the size of your database, you may be able to throw enough ram at the server to keep the entire database in RAM, which makes queries much faster.
• Try organizing your queries so you're filtering your data on the first table in your list. It looks like your first table is returning 17,000 records that need to be further filtered by the rest of the tables. This may be necessary, but perhaps the tables could be arranged differently.