How to choose multiple table structure for fastest query response

I have开发者_高级运维 a database in which I am listing events by geography (currently around 100000 events in the database). At the moment it's in a nicely normalised schema: an event in table tevent has a foreign key fkvenue which points at a venue in table tvenue which has a foreign key fkcity, and so on to tregion and tcountry.

The query to find the countries with the most events is therefore fairly complex, with no less than three inner joins:

select r.fkcountry,count(distinct e.id) from 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 by r.fkcountry order by count(distinct e.id) desc

I'm trying to find ways of speeding things up, and I thought it might be helpful to map the events directly by country. I've created a map table teventcountry, with the following much simpler resulting syntax:

select ec.fkcountry,count(distinct e.id) from tevent e inner join teventcountry ec on ec.fkevent=e.id group by ec.fkcountry order by count(distinct e.id) desc

To my surprise, this had the exact opposite effect: the new query took nearly five times as long as the older, much more complex query.

Can someone explain this, or point me at a good resource to help me gain an understanding of the speed of multi-table queries? Or even tell me a preferred pattern for this sort of thing?

(before anyone asks, I've taken care to do a "reset query cache" between runs in order to make the timing results valid, and yes, I have indexed everything properly!!!!)

Thanks

David

You need to look at how MySQL is evaluating the queries. To do this, use the built-in command EXPLAIN, which describes how MySQL executes a given select statement.

Here is the reference on EXPLAIN: http://dev.mysql.com/doc/refman/5.5/en/explain.html

You can use EXPLAIN's output to see the MySQL "query execution plan", including which indexes and joins it would use, and so on. From this you can interpret why a given query is fast or slow, and what you can do to improve it. Usually this means adding a particular index, or tweaking one so it can be used in your query.

You can Google "MySQL EXPLAIN" for a bunch of guides on using this powerful tool. Here is one such walk-through: http://forums.spry.com/howtos/1345-using-mysqls-explain-command.html