mysql - How to choose multiple table structure for fastest query response -

i have database in listing events geography (currently around 100000 events in database). @ moment it's in nicely normalised schema: event in table tevent has foreign key fkvenue points @ venue in table tvenue has foreign key fkcity, , on tregion , tcountry.

the query find countries events therefore complex, no less 3 inner joins:
select r.fkcountry,count(distinct e.id) tevent e inner join tvenue v on e.fkvenue=v.id inner join tcity cy on v.fkcity=cy.id inner join tregion r on cy.fkregion=r.id group r.fkcountry order count(distinct e.id) desc

i'm trying find ways of speeding things up, , thought might helpful map events directly country. i've created map table teventcountry, following simpler resulting syntax:
select ec.fkcountry,count(distinct e.id) tevent e inner join teventcountry ec on ec.fkevent=e.id group ec.fkcountry order count(distinct e.id) desc

to surprise, had exact opposite effect: new query took 5 times long older, more complex query.

can explain this, or point me @ resource me gain understanding of speed of multi-table queries? or tell me preferred pattern sort of thing?

(before asks, i've taken care "reset query cache" between runs in order make timing results valid, , yes, have indexed properly!!!!)

thanks

david

you need @ how mysql evaluating queries. this, use built-in command explain, describes how mysql executes given select statement.

here reference on explain: http://dev.mysql.com/doc/refman/5.5/en/explain.html

you can use explain's output see mysql "query execution plan", including indexes , joins use, , on. can interpret why given query fast or slow, , can improve it. means adding particular index, or tweaking 1 can used in query.

you can google "mysql explain" bunch of guides on using powerful tool. here 1 such walk-through: http://forums.spry.com/howtos/1345-using-mysqls-explain-command.html