How to store a big database

[cwarn23]

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.

[mwasif]

I found that anything over 15MB in one table takes too long to lookup

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?

[cwarn23]

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?

[mwasif]

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?

[cwarn23]

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:

SELECT * FROM `table` WHERE `col1`="somevalue"

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.

[mwasif]

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

CREATE TABLE `table_data` (
`col1` VARCHAR( 150 ) NOT NULL ,
`col2` VARCHAR( 150 ) NOT NULL ,
`col3` VARCHAR( 150 ) NOT NULL ,
`col4` VARCHAR( 150 ) NOT NULL
) ENGINE = MYISAM ;

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.

ALTER TABLE `table_data` CHANGE `col1` `col1` VARCHAR( 150 ) NOT NULL,
CHANGE `col2` `col2` VARCHAR( 150 ) NOT NULL,
CHANGE `col3` `col3` VARCHAR( 150 ) NOT NULL,
CHANGE `col4` `col4` VARCHAR( 150 ) NOT NULL;

Do you have an IDEX on col1? Creating an index on this column will enhance the performance.
ALTER TABLE `table_data` ADD INDEX ( `col1` );

[cwarn23]

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?

[mwasif]

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.

[cwarn23]

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.

[mwasif]

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

[Atli]

Hey.

If you have a table with a VARCHAR field, you can often improve search performance on it by changing the field to the CHAR type. It has a static length, which allows MySQL to calculate a static row size for all the rows, rather then having to calculate a row size for each row. This allows it to scan the rows faster.

You won't notice any difference tho. If you provide to sort a string, MySQL will automatically pad it with spaces, and then trim them when you fetch it. The only difference is that it will increase the storage space required.

Then you must use TEXT type if the length is more than 255.

That's not true anymore. VARCHAR used to be maximum 255 chars before 5.0, but now it can be a lot bigger.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

[cwarn23]

If you have a table with a VARCHAR field, you can often improve search performance on it by changing the field to the CHAR type.

So is the char field type faster than text field type? Because that's what I really need to know although it's a bugga it will consume more space for faster performance.

[Atli]

So is the char field type faster than text field type? Because that's what I really need to know although it's a bugga it will consume more space for faster performance.

Not really, no. The field itself isn't faster.

What makes it faster is that it will always have the same size for every row. If you create a CHAR(50) column and only put 20 characters into one of the fields, MySQL will still use 50 characters to store that field. It would append 30 spaces to the original 20 and store that. (And then remove the 30 spaces when you fetch the data, so no worries there.)

TEXT and VARCHAR (and VARBINARY) are variable-length fields, which shrink to fit the data each field stores. This means that when MySQL searches the table, it has to calculate the position of the data within the data-file on a per-row basis, whereas when you only use fixed-length fields, it can assume all the rows are the same size and calculate where the data is for the entire table before it starts searching.

So in short, yes, CHAR is faster than VARCHAR, but only if ALL the fields in the table are fixed-size. If you have 3 VARCHAR, TEXT or VARBINARY fields in a table, swapping one out won't change anything. It's all or nothing.

[Atli]

Please note tho, that the performance gain from using this method is usually very minor when compared to other optimization methods, such as properly indexing the fields.

A well placed index can be the difference between counting query speeds in minutes and milliseconds. (True story!)

[cwarn23]

Did you know that with todays cpu speeds the average computer can calculate the reverse lookup of any hash that contains up to two digits. So if there are >=2 digits being stored in a hash then the computer can calculate the original two digit string within less than a second. Just a thing I learnt when creating a dehasher in Java.

[cwarn23]

Did you know that with todays cpu speeds the average computer can calculate the reverse lookup of any hash that contains up to two digits. So if there are >=2 digits being stored in a hash then the computer can calculate the original two digit string within less than a second. Just a thing I learnt when creating a dehasher in Java.

Oops... Wrong thread for this post...

[mwasif]

Did you know that with todays cpu speeds the average computer can calculate the reverse lookup of any hash that contains up to two digits. So if there are >=2 digits being stored in a hash then the computer can calculate the original two digit string within less than a second.

Just a thing I learnt when replying to a MySQL thread :)