September 9th, 2000, 07:49 PM
My MySQL database is taking up too much memory for my non-dedicated server to hold (supposedly 700 MB). I need a way to condense this data, or the server will kick me off.
One table currently has 1.6 million rows of data, each one structured like this:
groupname VARCHAR (50) not null,
sitetitle VARCHAR (50) not null,
siteurl VARCHAR (250) not null,
rating DECIMAL (5,1),
ratingindex INT unsigned,
number INT unsigned not null,
PRIMARY KEY (groupname, sitetitle, siteurl));
What I really need to keep is a large list of URL's, site titles, ratings, and a number to index each one.
What can I change to save memory? If I remove the groupname and ratingindex fields, will the memory usage drastically change?
September 11th, 2000, 12:19 PM
assuming the index (number) is going to be unique you could get rid of your primary keys they don't server any function and then set number as your only primary key. This will slow down your queries though (if you're querying groupname, sitetitle or siteurl) In which case you can index one or two of those fields (but if you index all three you will end up right back where you started. Also what is ratingindex?
September 11th, 2000, 11:26 PM
You say one table is 16M records, but how many other tables do you have and what are they doing? Can you browse to where the actual table files are, or get your ISP to email the output of 'ls -la' for the directory where your tables are stored. I'm willing to bet there is a lot of wasted space. 700 MB seems rather big for a medium size database like yours. In the absence of this information, it's hard to know exactly what your problem is, but...
1. Run OPTIMIZE TABLE frequently on all tables in your database.
2. how much can you afford to change column types and properties? For example, your rating column shows DECIMAL (5.1), but if you have some sort of pre-defined group of ratings possibilities, you might be better off with ENUM, which can use a lot less space. You define two columns as INT, which can hold numbers up to 4 billion. If you can get away with MEDIUMINT (up to 16 million records), you would save a lot.
3. Also, your primary key seems a very wasteful. Try MyLameNic's advice and just set 'number' as the primary key.
Or, if you really need to index those other columns, MySQL allows you to index only a small part of the field for CHAR and VARCHAR columns, so you would just index the first 1 or 2 letters in each column (big spacesaver)