Hi,
I need a help...
Does anybody have experience with problems which require a lot of rows?
note: I'm using MySQL database...
Example #1:
Problem:
we have a lot of users, like one million... we need to create a system which will put some information about every user into the table (like username and password), there is limitation - max. rows per table (65534 fo MySQL) - how to cope with this?
Solution?
to make a system that is monitoring number of rows in table "users000" and when the row limit for that table is reached, it automaticaly creates a new table "users001" and start filling it...
when the script need to find the user - it have to go through all the tables...
note: need to assure that UNIQUE fields are really unique among all the tables...
Example #2:
Problem:
We have 1 000 tests (questions with predefined answers - a), b), c)...) and every test has a 100 questions - that is 1 000 x 100 = 100 000 questions - we need to monitor all answers for EVERY!! user - so we need to create 100 000 x 1 000 000 = 100 000 000 000 row long table... (using above algorithm leads us to 1 525 926 tables)...
Solution?
Huh! We need to rapidly decrease row requirements, but amount of tests and possible questions may vary (everything is dynamic and have to be flexible) - what with it?
Well... First we HAVE TO put all the questions as columns - but we have to assure that any amount of questions is possible (with max. columns limit of course), so we have to create a script which will check if there is enough amount of columns (one per question) and when more columns are required it alters table and add a new columns... the rest is same as above
So we need 'only' 1 000 x 1 000 000 = 1 000 000 000 rows = 15 260 tables (huh!); In bytes we'll need only one byte per question (100 bytes + 2 for primary key) 102x1000000000/1024^3=95GB (HUH!) (this is 100% over the DB size limit
);
Questions:
Any better idea how to solve it?
It there any max. tables limit in MySQL?
What are the following limits:
maximal tables: (?)
maximal rows per table: 65534 (?)
maximal cols per table: 3398 (?)
maximal DB size: (?) MB
optimal tables: (?)
optimal rows per table: (?)
optimal cols per table: (?)
optimal DB size: (?) MB
I need a help...
Does anybody have experience with problems which require a lot of rows?
note: I'm using MySQL database...
Example #1:
Problem:
we have a lot of users, like one million... we need to create a system which will put some information about every user into the table (like username and password), there is limitation - max. rows per table (65534 fo MySQL) - how to cope with this?
Solution?
to make a system that is monitoring number of rows in table "users000" and when the row limit for that table is reached, it automaticaly creates a new table "users001" and start filling it...
when the script need to find the user - it have to go through all the tables...
note: need to assure that UNIQUE fields are really unique among all the tables...
Example #2:
Problem:
We have 1 000 tests (questions with predefined answers - a), b), c)...) and every test has a 100 questions - that is 1 000 x 100 = 100 000 questions - we need to monitor all answers for EVERY!! user - so we need to create 100 000 x 1 000 000 = 100 000 000 000 row long table... (using above algorithm leads us to 1 525 926 tables)...
Solution?
Huh! We need to rapidly decrease row requirements, but amount of tests and possible questions may vary (everything is dynamic and have to be flexible) - what with it?
Well... First we HAVE TO put all the questions as columns - but we have to assure that any amount of questions is possible (with max. columns limit of course), so we have to create a script which will check if there is enough amount of columns (one per question) and when more columns are required it alters table and add a new columns... the rest is same as above
So we need 'only' 1 000 x 1 000 000 = 1 000 000 000 rows = 15 260 tables (huh!); In bytes we'll need only one byte per question (100 bytes + 2 for primary key) 102x1000000000/1024^3=95GB (HUH!) (this is 100% over the DB size limit
);Questions:
Any better idea how to solve it?
It there any max. tables limit in MySQL?
What are the following limits:
maximal tables: (?)
maximal rows per table: 65534 (?)
maximal cols per table: 3398 (?)
maximal DB size: (?) MB
optimal tables: (?)
optimal rows per table: (?)
optimal cols per table: (?)
optimal DB size: (?) MB