#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2000
    Posts
    108
    Rep Power
    15
    Hello.

    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,
    INDEX (number),
    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?

    Thanks!
    yoshi
    catgolf888@aol.com
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    9
    Rep Power
    0
    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?
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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)

    See http://www.mysql.com/documentation/m...n=CREATE_INDEX

Similar Threads

  1. Internet Explorer memory usage
    By lclim in forum Dev Shed Lounge
    Replies: 0
    Last Post: January 7th, 2004, 10:18 AM
  2. Run out of virtual memory (memory leak)
    By cessua in forum C Programming
    Replies: 3
    Last Post: December 23rd, 2003, 02:53 PM
  3. VB6 startup memory bloat
    By mvording in forum Visual Basic Programming
    Replies: 1
    Last Post: June 30th, 2003, 02:58 PM
  4. C, Linux, memory usage tracking help needed
    By dmittner in forum C Programming
    Replies: 0
    Last Post: June 17th, 2003, 02:03 PM
  5. strange memory problem
    By st4 in forum Apache Development
    Replies: 3
    Last Post: May 18th, 2001, 09:09 PM

IMN logo majestic logo threadwatch logo seochat tools logo