Member Avatar for dele454

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:


Having a stored procedure that would count each event per category based on this ER Diagram above orSecond 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 :)

events.gif 14.8 KB
  • 2 Contributors
  • 2 Replies
  • 105 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by dele454

Dani AI

Generated

As suggested, keep a single Events table (with a join/mapping table if events can have multiple categories). One table with a proper index on the category key scales far better than creating a separate physical table per category and keeps the schema maintainable. A simple, efficient count-per-category query looks like:

SELECT c.id, c.name, COUNT(e.id) AS cnt
FROM categories c
LEFT JOIN events e ON e.category_id = c.id
GROUP BY c.id;

Index the column you use for grouping/filtering (for example events.category_id) and verify the plan with EXPLAIN. That lets the server use the index rather than scanning full rows.

About the per-category-tables idea: it may give very fast individual COUNT(*) results for MyISAM tables (MyISAM stores table row counts), but it introduces schema bloat, harder queries, more file handles, and painful maintenance when categories change. For real sites with writes and concurrency, InnoDB’s row-level locking, transactions and foreign-key integrity are the better choice despite InnoDB not keeping a global row count the way MyISAM does (see the MySQL manual for engine contrasts) .

If front-end counts must be ultra-fast, use one of these proven approaches: maintain a denormalized counter on the category row (atomically update with each event insert/delete), update counters in application code or via triggers (test for contention), or cache counts in Redis/Memcached and refresh asynchronously. Always test with representative data, use EXPLAIN/ANALYZE, and prefer InnoDB when data integrity and concurrent writes matter.

Recommended Answers

All 2 Replies

Member Avatar for plazmo

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.

Member Avatar for dele454

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.