Should Large Tables use MyISAM ?

[Analogged]

Hello,

Im using MySQL 5.5 but all my databases still use MyISAM as the default. Some tables are getting quite large such as my shoutbox table having over 1 millions rows. Its looking like MyISAM is lagging for such occasion as trying to view the shoutbox archive and and still being able to read and write new rows. So im guessing i should be able to just convert this table to use InnoDB in hopes to help optimize it, or are there any reasons why i shouldn't.

Thanks
343i

[Brett L]

InnoDB would probably give you better performance, however, personally I would just prune some of the older shouts.

[Analogged]

Yeah pruning does seem like the logical choice but my community is really proud of the shout count so im trying to keep it as long as possible.

Thanks for the info, im gonna give it a try.

[Analogged]

Just thought id update this and say that i have succesfully converted the shoutbox shouts table to InnoDB while leaving the rest to still use MyISAM. Everything went smoothly and the archiving is working again with the shoutbox quite nicely for 1030k Shouts.

[Brett L]

If the number is really all you care about I would highly suggest looking at the PHP code, finding the output of the counter, adding a million to the number and then prune a million shouts.
I'm not fully familiar with that 3rd party addon however, just looking at it on your site I don't see any kind of archive feature so it's not like your members can access those old shouts anyway.

That's personally how I would handle the situation. If InnoDB is giving you the performance that you want then by all means continue with what you're doing. Just listing it as an option if you are still having issues.

[Analogged]

Thanks for the suggestion, i didn't think of that but that is definitely the road i will take if any more stability errors pop up. Cause your right just the actual number for total shouts is needed and not the actual shouts that no one would ready anyways. As for the archive iv disabled access to that feature and currently am only allowing my staff access as before switching to InnoDB just making a search to bring up all the shouts in 1 day would lock the table up and lag the shoutbox but now with the testing me and my mods have done it seems to be able to bring up a months worth of shouts without causing any lagging or other issues.

But just for my own satisfactory purposes i tried pulling up all 1000k within a single search and it ended with me killing the process on the server as i kind of expected since the server spiked a huge load.

I may see if i can edit the application to only allow users to search through the archives only so far so no one by mistake could crash it but even then i might still only allow my staff to use the archive.

Thanks for the idea
343i