CODEWITHSUNDEEP
phpmysql
MANnDAaR
MANnDAaR

Reputation: 2590

mysql table rows limit?

I'm new to mysql & I have coded my first php-mysql application.

I have 30 devices list from which I want user to add their preferred devices into his account.

So, I created mysql table "A" where some device-id's are being stored under particular userID like this 

UserID  |  Device Id

1       |  33
1       |  21
1       |  52
2       |  12
2       |  45
3       |  22
3       |  08
1       |  5 
more.....

Say, I have 5000 user-ids & 30 devices-ids.

& If each user has 15 device-id's (on-average) under his account records.

Then it will be 5000 X 15 = 75000 records under Table "A"

So, My question, Is there any limit on how many records can we store in to mysql table ?

Is my approach for storing records as mentioned above is correct ?? whether it will affect query performance if more users are added ?

OR there is any better way to do that ?

Upvotes: 4

Views: 1015

Answers (2)

Scotch
Scotch

Reputation: 3226

It's very unlikely that you will approach the limitations of a MySQL table with two columns that are only integers.

If you're really concerned about query performance, you can just go ahead and throw an index on both columns. It's likely that the cost of inserting / updating your table will be negligible even with an index on the device ID. If your database gets huge, it can speed up queries such as "which users prefer this device". Your queries that ask "what devices do this user prefer" will also be fast with an index on user.

I would just say to make this table a simple two column table with a two-part composite key (indexed). This way, it will be as atomic as possible and won't require any of the tom-foolery that some may suggest to "increase performance."

Keep it atomic and normal -- your performance will be fine and you won't exceed any limitations of your DBMS

Upvotes: 3

harsh8888
harsh8888

Reputation: 477

I don't see anything wrong in it. If you are looking forward to save some server space, then don't worry about it. Let your database do the underlying job. Index your database properly with an ID - int(10) primary auto increment . Think about scalability when it is needed. Your first target should be to complete the application that you are making. Then test it. If you find that it is causing any lag, problem, then start worrying about the things to solve the problem. Don't bother yourself with things that probably you might not even face.

But considering the scale of your application (75k to 1 lac records), it shouldn't be much of a task. Alternatively you can have a schema like this for your users

(device_table)

device_id
23
45
56

user_id |  device_id
1       |  23,45,67,45,23
2       |  45,67,23,45

That is storing device_ids in an array and then getting the device_id for particular user as

$device_for_user=explode(',',$device_id)

Where of course device_id is retrieved from mysql database. so you'll have 

$device_for_user[0]=23
$device_for_user[1]=45

amd so on.

But this method isn't a very good design or an approach. But just for your information, this is one way of doing it

Upvotes: 1

Related Questions