Database slow retriving/updating/inserting problem with more than 5mil records in each table

Question

How to structure database to avoid slowdowns? (Engine: MyISAM)

Currently i have database with more than 5milion records in one table that causes slow data retrieving. I'm currently searching for ways to structure database to avoid this kinds of database. (Database Engine MyISAM)

Tables that cause problems are posts and comments having more than 5mil records in each.

I had an idea when using text file as storage when saving records by date, so that each file contained enough data that wasn't slowing retrieving and saving processes, But with databases i don't know what to do :(

Is there any way to save data (approx 5mil records in each) in MySQL database not to cause slow retrieving, inserting or updating data?

"posts" Structure

    CREATE TABLE IF NOT EXISTS `ibf_posts` (
  `pid` int(10) NOT NULL auto_increment,
  `append_edit` tinyint(1) default '0',
  `edit_time` int(10) default NULL,
  `author_id` mediumint(8) NOT NULL default '0',
  `author_name` varchar(32) default NULL,
  `use_sig` tinyint(1) NOT NULL default '0',
  `use_emo` tinyint(1) NOT NULL default '0',
  `ip_address` varchar(16) default NULL,
  `post_date` int(10) default NULL,
  `icon_id` smallint(3) default NULL,
  `post` text,
  `queued` tinyint(1) NOT NULL default '0',
  `topic_id` int(10) NOT NULL default '0',
  `post_title` varchar(255) default NULL,
  `new_topic` tinyint(1) default '0',
  `edit_name` varchar(255) default NULL,
  `post_key` varchar(32) default NULL,
  `post_parent` int(10) NOT NULL default '0',
  `post_htmlstate` smallint(1) NOT NULL default '0',
  `post_edit_reason` varchar(255) default NULL,
  PRIMARY KEY  (`pid`),
  KEY `topic_id` (`topic_id`,`queued`,`pid`,`post_date`),
  KEY `author_id` (`author_id`,`topic_id`),
  KEY `post_date` (`post_date`),
  KEY `ip_address` (`ip_address`),
  KEY `post_key` (`post_key`),
  FULLTEXT KEY `post` (`post`),
  FULLTEXT KEY `post_2` (`post`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Query:

SELECT p.*, pp.*,.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title,m.hide_email, m.warn_level, m.warn_lastwarn, m.points, m.topics_started, m.skin,
           me.msnname,me.aim_name,me.icq_number,me.signature, me.website,me.yahoo,me.location, me.avatar_location, me.avatar_type, me.avatar_size, m.members_display_name, m.custom_post_css, m.custom_right_img
           m.custom_post_color
            FROM posts p
               LEFT JOIN members m ON (m.id=p.author_id)
             LEFT JOIN profile_portal pp ON (m.id=pp.pp_member_id)
               LEFT JOIN member_extra me ON (me.id=m.id)
            WHERE p.pid IN(--post ids here) 
            ORDER BY --ordering here

Answer 1

5M is not that much.

Probably you indexed the table wrong.

Please post your query and we'll probably tell you how to improve it.

Update:

SELECT  p.*, pp.*,.id,m.name,m.mgroup,m.email,m.joined,m.posts, m.last_visit, m.last_activity,m.login_anonymous,m.title,m.hide_email, m.warn_level, m.warn_lastwarn, m.points, m.topics_started, m.skin,
        me.msnname,me.aim_name,me.icq_number,me.signature, me.website,me.yahoo,me.location, me.avatar_location, me.avatar_type, me.avatar_size, m.members_display_name, m.custom_post_css, m.custom_right_img
        m.custom_post_color
FROM    posts p
LEFT JOIN
        members m
ON      m.id = p.author_id 
LEFT JOIN
        profile_portal pp
ON      pp.pp_member_id = m.id
LEFT JOIN
        member_extra me
ON      me.id = m.id
WHERE   p.pid IN (--post ids here) 
ORDER BY
        --ordering here

Make sure that:

• members.id is a PRIMARY KEY
• member_extra.id is a PRIMARY KEY
• You have an index on profile_portal.pp_member_id

Also you omitted the ORDER BY clause but this clause is important too, using indexes can improve it as well.

Answer 2

Do you have any indexes? How are you selecting the data (by Primary Key only(?

Answer 3

EXPLAIN PLAN will tell you how the query engine is doing it. If you see "table scan", you know you need indexes.

Answer 4

5M rows in one table is not that much, how long your queries are taking? I suspect you may have some problems with indexing. EXPLAIN statement may help to find out what you queries are actually doing.

If you have properly indexed tables and sane queries, you could look into partitioning..

Edit:

You could try if adding INDEX(pid, author_id) or INDEX(author_id, pid) on table ibf_posts helps.