Manik Surtani <manik@stripped> wrote:
> I have an application (an online bookstore) which
> uses mysql.
<snip>
> I attempted a sizing of the database on our local
> testbed webserver (a dinky intel 200Mhz with 64Mb
> of Ram) and it is bloody slow, even with just
> 170,000 prduct records instead of 1.1 million
> .....
Sounds like you have general optimization needs. 170k records is nothing,
if you are using reasonable indices for the query. To test some
scalability constraints, I've built tables with millions of records, and
found that queries are still very fast.
Unfortunately, you don't give enough info to tell why things are slow.
Things that I know to watch out for include: full table scans (queries that
can't use keys), asking the server to sort the world, doing lots of inserts
and updates (especially when you have unnecessary indices). Many of these
things are somewhat subtle - for instance, col LIKE "Name%" can use the
index on col, but col LIKE "%Name%" will require a full table scan.
Good places to start include posting the table-creation command, and
running EXPLAIN on your queries. EXPLAIN is very helpful, both because it
can point out where you need an index, and it can point out that the index
you thought you needed isn't being used anywhere (in which case it'll slow
down any writes to the table).
Later,
scott
