Which is better in terms of optimization?
 

I just need your take on which of these two scenarios will yield quicker results with optimization in mind. On the front end there are a lot of pages with counts per category of events e.g Restaurants (40) | Pubs (2) | Gallery (6)

First Scenario:

Attachment 5594

Having a stored procedure that would count each event per category based on this ER Diagram above or


Second Scenario:

Have the Event & Category tables as it is from above but have a series of Category tables for each individual Category. So Restaurant will have it own table, pub etc and house only the EventID for each event.

On counting instead of running a Stored procedure that scans through the whole event table + Category table like Scenario 1, I'll run one stored procedure that counts all these MyISAM Category table records to count how many records there are in each category table.


I need to know which will yield a faster outcome, which will be more memory intensive. These Categories will hardly change. There might be an additional category but that wont be an issue updating the Category table and creating a new eventcategory table.

Kindly let me have comments :)

Re: Which is better in terms of optimization?
 

Use the first scenario.
Keep all the events in one table and all the categories in one table.
It will be faster and easier to work with.

Re: Which is better in terms of optimization?
 

Thanks Plazmo. I appreciate the input.
I also need to ask: Data integrity is very paramount in my application. If a lot of my tables are of InnoDB type, would there be any performance lags as compared to using MyISAM