performance vs many records

[homerUK]

Hi guys,

I'm after a little advice. I currently have 300 records in table "metrics". These metrics are assigned to users. So each user has access to the 300 metrics. by default, my SQL will load all 300 on a page for the user.

I am now wanting the users to be able to say which they want to appear. So, out of the 300 they can tick the ones they want to see.

What's the best way of doing this?

I have thought about having a table called metrics_to_user which would store the user ID and a whole list of the metrics they choose. By default, every user would then need 300 records in this table? then they untick ones they dont want to see.

Or another way is just to store just the ones they DONT want to see. So when I am loading the page, I do some SQL to get all 300, but somehow forget the ones they dont want.

what is the best approach?!
thanks for any information :)

[guelphdad]

1 table for users
1 table for metrics
1 table as you suggest for metrics to users. 1 row in this table for each metric a user wants. even if they want 200 you would have 200 rows for them.

[homerUK]

yeah that's what i was thinking. Thanks :)

So it wouldnt matter if there ended up being thousands of rows?

[guelphdad]

No not really. If your tables are properly indexed that is one thing to think about. Then on top of that look into database optimization or tuning, caching levels, that sort of thing.

Mysql can handle millions upon millions of rows of data under the right circumstances and hardware. check the list of huge customers using mysql on their website.

[felgall]

The database will only slow down if your tables are not indexed properly or they contain hundreds of thousands of rows where a large percentage meet specific criteria.