How to optimise a large database

[mrwooster]

I have a database which contains approx 80,000 records and receives about 2000 new records every day. The records are regularly queried and the database is approx 120MB in size.

My server is using up a lot of RAM and I am thinking that it might well be due to the the database... is there any way to optimise the database to reduce the ram usage? I have heard of cases where the table is split up into several smaller tables which hold, for example, table_1 -> records 1-5000, table_2 -> records 5000-10,000 , and so on....

Would this help reduce memory use? Or are there any other techniques?

Thanks

[r937]

2000 new rows per day = less than a million in a year

this is not a small table, but it's also not a very large table either

how many tables in your database? sounds like only one

what kind of indexes have you defined? are any of your queries running slowly, and if so, have you done an EXPLAIN on them?

[mrwooster]

Hey,

The database has 4 tables but the other 3 are tiny in comparison (they hold user preferences etc....).

There is only 1 index defined on the table.... I am not an expert in Database design so I am not sure if this is good or bad... should I be defining more indexes?

I get the impression that the queries are running a bit slow... I can browse pages of the site very fast, but as soon as you arrive on a page which is making database requests, the page takes a bit longer to load.... however... I have noticed that phpMyAdmin and my wordpress blog both run very fast and so is it possible that I have done some sloppy coding? Or does phpmyadmin cache database info?

This is the output of the EXPLAIN:

+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment | 
| timestamp   | int(10) unsigned    | NO   |     | NULL    |                | 
| code        | varchar(10)         | NO   |     | NULL    |                | 
| owner       | int(10) unsigned    | YES  |     | NULL    |                | 
| title       | varchar(255)        | YES  |     | NULL    |                | 
| description | text                | YES  |     | NULL    |                | 
| author      | varchar(100)        | YES  |     | NULL    |                | 
| location    | varchar(255)        | YES  |     | NULL    |                | 
| hash        | varchar(128)        | YES  |     | NULL    |                | 
| text        | int(10) unsigned    | YES  |     | 0       |                | 
| file        | int(10) unsigned    | YES  |     | 0       |                | 
| expiry      | int(10) unsigned    | YES  |     | NULL    |                | 
| views       | int(10) unsigned    | YES  |     | NULL    |                | 
| password    | varchar(128)        | YES  |     | NULL    |                | 
| ip          | varchar(12)         | YES  |     | NULL    |                | 
| origin      | tinyint(3) unsigned | NO   |     | NULL    |                | 
| type        | tinyint(3) unsigned | YES  |     | NULL    |                | 
| encrypted   | tinyint(4)          | NO   |     | 0       |                | 
| client      | varchar(255)        | YES  |     | NULL    |                | 
+-------------+---------------------+------+-----+---------+----------------+

I really appreciate your help

Ty

[r937]

that's not an EXPLAIN, that's a phpmyadmin display of your table

an EXPLAIN is obtained by putting the word EXPLAIN in front of the word SELECT for a given query, and it will tell you how the query is executed

regarding indexes, please read this: http://articles.sitepoint.com/articl...ql-application

[mrwooster]

ah.... apologies for my naivety. Thanks for the link, off to work now so will have a look when I get back.

Thanks

[mrwooster]

r937 .... I just read the article you posted and all I can say is ... WOW ... thanks a lot

[freezea]

Please refer to the url below:
http://www.developer.com/db/article....L-Database.htm