Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008
    Location
    Cape town, South Africa
    Posts
    11

    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:

    Click image for larger version. 

Name:	events.gif 
Views:	39 
Size:	14.8 KB 
ID:	8259

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Scenario 1 with an index on CategoryId would be a much better design, and should perform just as well (though I know nothing of mySQL which I'm guessing you are using).

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Tony - don't you think an index on category name? In that case the count wouldn't need to touch the leaf level - just the B-Tree (assuming this is applicable to mySQL - I too can claim utter ignorance of this RDBMS).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Scratch that. Misread the requirement.....

    Although it does link back to a discussion of surrogates and natural keys from the other day of course....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2008
    Location
    Cape town, South Africa
    Posts
    11
    Scenario 1 with an index on CategoryId would be a much better design, and should perform just as well (though I know nothing of mySQL which I'm guessing you are using).
    Yes i am using MYSQL.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see also this thread on some other site which will remain unnamed, where you asked the exact same question

    Last edited by r937; 03-27-08 at 16:04.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2008
    Location
    Cape town, South Africa
    Posts
    11
    r937 Can you please delete the link. I am trying to source opinions from 2 seperate forums. Please lets keep sitepoint out of this discussion

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sure no problem

    i just thought i would save the guys here from taking the time to give you an opinion that someone else has already given you elsewhere
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2008
    Location
    Cape town, South Africa
    Posts
    11
    The intention is sourcing varied opinions. I am being given a different advise here. Can you kindly take the thread out completely it simply doesnt make any sense with the rephrasing. I didnt post a similar thread in some other forum because you are also there posting links about similar questions asked elsewhere. You dont know the intention of the thread poster!
    Last edited by dele454; 03-27-08 at 16:30.

  10. #10
    Join Date
    Feb 2008
    Posts
    43
    I would see it as Scenario 1 with an index on CategoryID and then a Join on Events and EventsCategory tables with a group by CategoryID

    However I don't have the knowledge to tell you if this is a faster way than scenario 2

  11. #11
    Join Date
    Jan 2008
    Location
    Cape town, South Africa
    Posts
    11
    Thanks for the comment will implement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •