Weighing up performance and data detail in MySQL

[eluser]charlieD[/eluser]
I'm setting up an app and am trying to decide between a couple of different approaches for setting up the MySQL database.

The first approach would mean nicely detailed data which I don't think I'd use, for the moment at least.

The second approach would be to keep a summary table with the data I will be using, and this would save a lot of rows being created.

I can see the main table becoming very large (possibly millions of rows), and going with the detailed data would mean this table could be 5 or 10 times the size. The table will be queried very frequently.

My question is when it would be best to keep the detailed data which technically is a better database design, or go for saving query time and use the summary data.

I hope this makes sense,

Thanks

[eluser]lukeinjax[/eluser]
If I understand correctly, you are looking at a set of normalized tables vs. a single de-normalized table for the sake of performance? If I were you, I'd do some testing. Load up the data into 2 normalized tables (you can just write a loop to insert the data), add the appropriate indexes and see how long it takes. If it's within an acceptable range, then use that approach. The metrics should be fairly easy to extract with CI's built in profiler function.

As long as BOTH tables are not huge, the performance impacts of having 2 normalized tables should not be too significant I wouldn't think, as long as the proper indexes are created.

[eluser]ehicks727[/eluser]
Millions of rows is not a problem. I handle millions of rows all the time with MySQL. You should always go as normalized as is reasonable.

What you need to pay attention to is your indexes. If your indexes are optimized, then performance shouldn't be a problem... unless you have a crappy server