Hi and I have been working on a project involving a rather large database. My question is "For a database with over 20529860 rows, is it better to have 3330 tables spreaded equally or to split into multiple databases or would it be better to have 49950 tables in the database containing the information?" The reason I ask is that having all those rows in the one table makes it really slow with the Where clause and I'm not sure which way is the best way to design this database with mysql. I found that anything over 15MB in one table takes too long to lookup so if I had to guess I would spread it over the 49950 tables ((255-33)*15*15) but sounds like a ridiculous number of tables to store the one set of data. So does anybody have any ideas on how to store potentially 1,000,000,000 rows in mysql. Each row only contains a few bytes. Thanks.

I never faced any problem with much larger tables (in GBs).

Are you properly indexing your tables? For performance, you can split the tables on year/month basis if you have datatime field. Otherwise you can use MySQL 5.1's partitioning.

How many columns do you have in a table?

Then how many rows do you have and how much cpu? In each of my tables is only 4 text columns and one int column. Also the word NULL occurs as the value most of the time with my current structure if that makes any difference.

So I have 2.66GHz dual core with millions of rows. What are your specs for cpu and num of rows in your large database?

I am dealing with tables with 50+ million rows with much more columns. But they do not contain more than 1 text column. Are columns of type text or varchar?

The performance also depends on the available RAM to MySQL. I have quad core server with 12GB RAM.

How do you seek data (SELECT)? What does you have in your WHERE cluase?

Well basically on my first attempt with I might try a similar approach again was a table of 4 columns just plain text type (not varchar). They all have variable length and I can never seem to get varchar() to work with the number I put in the bracket in phpmyadmin. I think that number might represent the length but I have variable length. Also the length of each cell never exceeds 90 characters. The basic mysql statement I use is as follows:
Just note that I don't always use the characters on the keyboard for the "somevalue" because it is encrypted to use most of the ansci table.

Also I'm testing on a computer with 4GB of ram and I did the math turns out my database failed at around 150 thousand rows. So do you have any ideas on what I should do or what how to set the columns to something better if needed?
Thanks.

Creating a table with VARCHAR columns of a specified length is very easy. e.g

Otherwise you can change the current table column types to VARCHAR with the following command. Keeping future needs in the mind, I have making varchar columns of length 150. You can adjust them according to your needs. The following query will take a long time according to your table size.
Do you have an IDEX on col1? Creating an index on this column will enhance the performance.
ALTER TABLE `table_data` ADD INDEX ( `col1` );

Then for the 150 you used, does that specify the string length? If so then is it possible to have a variable string length where different rows have different lengths?

Yes, 150 is the maximum string length that can be strored in this column. This can be any value upto 255 for VARCHAR. You can not manually set the length for each row, MySQL adjust it automatically for VARCHAR. Read the details in MySQL manual.

I will give it a try and just in case I need to know, what do I do if my string length exceeds 255. Also does the string length effect the storage requirements as in does this make all rows have equal storage size? Thanks for the great helps so far! I'll be back tomorrow.

Then you must use TEXT type if the length is more than 255. VARCHAR consumes the space according to the string length.