Greetings, I am working on a project which contains 6 tables in my MySQL database. One of those tables grew to be about 30 columns large and may grow even further and will have a lot of empty cells for an average entry. I want to plan things out ahead of time before the launch in case the website becomes successful and the database grows to become much larger, say 10+ million rows for this one table that has 30+ columns. What are some tips to keep the database and website running smoothly when it becomes huge? Is there a good guide anywhere for this. Thanks
These are a few good resources. There's ton's more out there. http://www.mysqlperformanceblog.com/ http://dev.mysql.com/doc/refman/5.0/en/optimization.html http://forge.mysql.com/wiki/Top10SQLPerformanceTips To start off, I would say, use InnoDB. Design your tables in a relational manner and do not de-normalize your db structure. Make sure you understand how to properly design a table and proper (not-excessive) indexing. Make sure you know how to optimize your queries. Don't use select * etc... Use stored procedures and triggers and other built-in functions if possible. Use the resources above to learn how to design the core parameters like buffer, cache, and log sizes. Host your server on quality hardware if possible. This means fast drives and a lot of RAM.
In addition to what jestep wrote, you might also consider using less columns whilst storing multiple entries separated by some delimiter (e.g. pipeline) in single cells, minimising the overhead. Of course, if you intend to produce searchable interfaces based on these, leave them as they are. Don't forget about the appropriate filed types (as in, don't use "text" when "varchar(255)" is sufficient) etc.