[Resolved] MySQL capacity

[sarahk]

I've got a MySQL database which is 30MB (I've worked with larger)

but one table is quite big:

Space usage : Type Usage
Data 12,633 KB
Index 7,826 KB
Total 20,459 KB

Format dynamic

Rows 227,827

Row length ø 56

Row size ø 92 Bytes

Next Autoindex 228,141

I've worked with tables which take more MB no probs but this is probably the most number of rows.

This month has seen a dramatic increase in content and my site has become almost unusuable as the reports on this data are on all pages.

I've been through and checked that I'm releasing my results but it's still running very slow. Timesout normally.
phpMyAdmin 2.5.2-pl1
MySQL 3.23.54

Am I on the right track chasing my database?
I figured I was because even MySQL runs slowly doing a record update on this table.
I think my indexes are fine, on primary and foreign keys.
My ISP did a big server upgrade about a week ago.

Any ideas?

[mhalloran]

Hi -

Can you post your select statement, table structure of tables involved including index and execution time?

That's a lot of rows but I'm sure with proper indexing and an optimized select, you should be able to retrieve the data quickly.

[sarahk]

mhalloran

thanks for the offer, managed to get my webhost to admit to the server problem and it seems better now.

Sarah