Reduce number of joins in mysql

I have 12 fixed tables (group, local, element, sub_element, service, ...), each table with different numbers of rows.

The columns 'id_' in all table is a primary key (int). The others columns are of datatype varchar(20). The maximum number of rows in these tables are 300.

Each table was created in this way:

CREATE TABLE group 
(
    id_G int NOT NULL,
    name_group varchar(20) NOT NULL,
    PRIMARY KEY (id_G)
);

|........GROUP......|    |.......LOCAL.......|    |.......SERVICE.......|          
| id_G | name_group |    | id_L | name_local |    | id_S | name_service | 
+------+------------+    +------+------------+    +------+--------------+
|   1  |   group1   |    |   1  |   local1   |    |   1  |   service1   |
|   2  |   group2   |    |   2  |   local2   |    |   2  |   service2   |

And I have one table that combine all these tables depending on user selects. The 'id_' come from fixed tables selected by the user are recorded into this table.

This table was crate in this way:

CREATE TABLE group 
(
    id_E int NOT NULL,
    event_name varchar(20) NOT NULL,
    id_G int NOT NULL,
    id_L int NOT NULL,
    ...
    PRIMARY KEY (id_G)
);

The tables (event) look like this:

|....................EVENT.....................|          
| id_E | event_name  | id_G | id_L | ... |id_S | 
+------+-------------+------+------+-----+-----+
|   1  |  mater1     |   1  |   1  | ... |  3  |
|   2  |  master2    |   2  |   2  | ... |  6  |

This table get greater each day, an now it has about thousunds of rows. Column id_E is the primary key (int), event_name is varchar(20).

This table has, in addition of id_E and event_name columns, 12 other columns the came from the fixed tables.

Every time than I need to retrieve information on the event table, to turn more readable, I need to do about 12 joins.

My query look like this where i need to retrieve all columns from table event:

SELECT event_name, name_group, name_local ..., name_service 
FROM event 
INNER JOIN group on event.id_G = group.id_G
INNER JOIN local on event.id_L = local.id_L
...
INNER JOIN service on event.id_S = service.id_S
WHERE event.id_S = 7 (for example)

This slows down my system performance. Is there a way to reduce the number of joins? I've heard about using Natural Keys, but I think this is not a good idea to form my case thinking in future maintenance.

My queries are taking about 7 seconds and I need to reduce this time. I changed the WHERE clause and this caused not affect. So, I am sure that the problem is that the query has so many joins.

Could someone give some help? thanks a lot...

Solution

MySQL has a great keyword of "STRAIGHT_JOIN" and might be what you are looking for. First, each of your lookup tables (id/description) I have to assume already have an index on the ID column since that is primary key.

Your event table is the one you are querying as the primary basis of the details and joining to the lookups per their respective IDs. As long as your WHERE clause applicable to the EVENT table is optimized, such as the ID you are looking for, it SHOULD be virtually instantaneous.

If it is not, then it might be that MySQL is trying to think for you and take one of the secondary lookup tables and make it a primary basis of the query for whatever reason, such as much lower record count. In this case, add the keyword and try it..

SELECT STRAIGHT_JOIN  ... rest of your query

This tells MySQL to do the query in the order you gave it, thus the Event table first and it's where clause on the ID. It should find that one thing, then grab all the corresponding lookup descriptions from the other tables.