I have an agency table, which has a many-to-many relationship with zones, countries, regions, cities, industries, professions, sectors, specialisms and sub_specialisms.

There is also a one-to-many relationship with offices and contacts.

Basically what I wanted to do is create a results table showing particular records from those tables.

I have created indexes on every foreign key in the tables. This is my query:

SELECT agencies.agency, 
       agencies.website_url, 
       agencies.status, 
       agencies.SIZE, 
       agencies.id, 
       officedata.id, 
       contactdata.name, 
       contactdata.surname, 
       contactdata.job_title, 
       contactdata.email, 
       contactdata.mobile 
FROM   agencies 
       LEFT JOIN (SELECT agencies_industries.agency_id, 
                         agencies_industries.industry_id 
                  FROM   agencies_industries) AS industrydata 
         ON agencies.id = industrydata.agency_id 
       LEFT JOIN (SELECT agencies_professions.agency_id, 
                         agencies_professions.profession_id 
                  FROM   agencies_professions) AS professiondata 
         ON agencies.id = professiondata.agency_id 
       LEFT JOIN (SELECT agencies_sectors.agency_id, 
                         agencies_sectors.sector_id 
                  FROM   agencies_sectors) AS sectordata 
         ON agencies.id = sectordata.agency_id 
       LEFT JOIN (SELECT agencies_seniorities.agency_id, 
                         agencies_seniorities.seniority_id 
                  FROM   agencies_seniorities) AS senioritydata 
         ON agencies.id = senioritydata.agency_id 
       LEFT JOIN (SELECT agencies_zones.agency_id, 
                         agencies_zones.zone_id 
                  FROM   agencies_zones) AS zonesdata 
         ON agencies.id = zonesdata.agency_id 
       LEFT JOIN (SELECT agencies_countries.agency_id, 
                         agencies_countries.country_id 
                  FROM   agencies_countries) AS countrydata 
         ON agencies.id = countrydata.agency_id 
       LEFT JOIN (SELECT agencies_regions.agency_id, 
                         agencies_regions.region_id 
                  FROM   agencies_regions) AS regiondata 
         ON agencies.id = regiondata.agency_id 
       LEFT JOIN (SELECT agencies_cities.agency_id, 
                         agencies_cities.city_id 
                  FROM   agencies_cities) AS citydata 
         ON agencies.id = citydata.agency_id 
       LEFT JOIN (SELECT agencies_specialisms.agency_id, 
                         agencies_specialisms.specialism_id 
                  FROM   agencies_specialisms) AS specialismdata 
         ON agencies.id = specialismdata.agency_id 
       LEFT JOIN (SELECT agencies_sub_specialisms.agency_id, 
                         gencies_sub_specialisms.sub_specialism_id 
       开发者_StackOverflow社区           FROM   agencies_sub_specialisms) AS subdata 
         ON agencies.id = subdata.agency_id 
       LEFT JOIN (SELECT offices.id, 
                         offices.agency_id, 
                         offices.hq 
                  FROM   offices 
                  WHERE  offices.hq = "1") AS officedata 
         ON agencies.id = officedata.agency_id 
       LEFT JOIN (SELECT contacts.name, 
                         contacts.surname, 
                         contacts.agency_id, 
                         contacts.job_title, 
                         contacts.email, 
                         contacts.mobile 
                  FROM   contacts) AS contactdata 
         ON agencies.id = contactdata.agency_id 

When executing it, the site loads and loads and after 5 minutes it crashes. I have no idea why it takes so long. In the processes in phpMyAdmin it says "copying to tmp table". After killing this process the site finishes loading with an error. Sometimes after executing this query whole phpMyAdmin doesn't work.

Do you have any idea how to make it work normal?

PS

In the database there are only about 20 or less rows in each table. Also when supplying WHERE clause at the end with a dummy data, the query executes in 10 seconds showing no results. When I supply data fitting only one result, it executes in about 20 seconds.

Update

Ok sorry I accepted the answer too fast because I didn't explain exactly why I need to left join those tables which are not used in my previous example.

The point is I need them to be used too, because all of this is a search system - someone would like to search for an agency where it's zone is Africa - in the results it is not displayed but it needs to be selected.

When I changed my query to this:

SELECT agencies.agency
                    ,agencies.website_url
                    ,agencies.STATUS
                    ,agencies.size
                    ,agencies.id
                    ,agencies.status
                    ,OfficeData.id
                    ,ContactData.name
                    ,ContactData.surname
                    ,ContactData.job_title
                    ,ContactData.email
                    ,ContactData.mobile
                    FROM agencies
                    LEFT JOIN offices AS OfficeData ON agencies.id = OfficeData.agency_id
                    AND OfficeData.hq = "1"
                    LEFT JOIN contacts AS ContactData ON agencies.id = ContactData.agency_id
                    LEFT JOIN agencies_professions AS ProfessionData ON agencies.id = ProfessionData.agency_id
                    LEFT JOIN agencies_seniorities AS SeniorityData ON agencies.id = SeniorityData.agency_id
                    LEFT JOIN agencies_sectors AS SectorData ON agencies.id = SectorData.agency_id
                    LEFT JOIN agencies_countries AS CountryData ON agencies.id = CountryData.agency_id
                    LEFT JOIN agencies_regions AS RegionData ON agencies.id = RegionData.agency_id
                    LEFT JOIN agencies_sub_specialisms AS SubData ON agencies.id = SubData.agency_id
                    LEFT JOIN agencies_cities AS CityData ON agencies.id = CityData.agency_id
                    LEFT JOIN agencies_zones AS ZoneData ON agencies.id = ZoneData.agency_id
                    LEFT JOIN agencies_specialisms AS SpecialismData ON agencies.id = SpecialismData.agency_id
                    LEFT JOIN agencies_industries AS IndustryData ON agencies.id = IndustryData.agency_id

It still loads till crashing the server.

In the processes it says now "Sending data"

There are two problems with your query. 1) There are a lot of needless subselects. 2) You are LEFT JOINing on tables you never select data from.

Try this instead:

SELECT agencies.agency
    ,agencies.website_url
    ,agencies.STATUS
    ,agencies.size
    ,agencies.id
    ,OfficeData.id
    ,ContactData.NAME
    ,ContactData.surname
    ,ContactData.job_title
    ,ContactData.email
    ,ContactData.mobile
FROM agencies
LEFT JOIN offices AS OfficeData ON agencies.id = OfficeData.agency_id
    AND OfficeData.hq = "1"
LEFT JOIN contacts AS ContactData ON agencies.id = ContactData.agency_id

After some discussion, I think this might work:

SELECT A.agency, A.website_url, A.status, A.size, 
       A.id, O.id, C.name, C.surname, 
       C.job_title, C.email, C.mobile 
  FROM agencies A
  LEFT JOIN offices O                     ON (A.id = O.agency_id)
  LEFT JOIN contacts C                    ON (A.id = C.agency_id)
 WHERE O.hq = '1'
   AND A.id IN (
       SELECT agency_id FROM agencies_industries WHERE <criteria for industries>
       UNION SELECT agency_id FROM agencies_professions WHERE <criteria for professions>
       UNION SELECT agency_id FROM agencies_sectors WHERE <criteria for sectors>
       UNION SELECT agency_id FROM agencies_seniorities WHERE <criteria for seniorities>
       UNION SELECT agency_id FROM agencies_zones WHERE <criteria for zones>
       UNION SELECT agency_id FROM agencies_countries WHERE <criteria for countries>
       UNION SELECT agency_id FROM agencies_regions WHERE <criteria for regions>
       UNION SELECT agency_id FROM agencies_cities WHERE <criteria for cities>
       UNION SELECT agency_id FROM agencies_specialisms WHERE <criteria for specialisms>
       UNION SELECT agency_id FROM agencies_sub_specialisms WHERE <criteria for sub_specialisms>);

You will need to replace each of the with your search criteria.

If you want to plug in criteria dynamically, you can use dummy false conditions OR'd with your plugged in criteria, like this:

SELECT A.agency, A.website_url, A.status, A.size, 
       A.id, O.id, C.name, C.surname, 
       C.job_title, C.email, C.mobile 
  FROM agencies A
  LEFT JOIN offices O                     ON (A.id = O.agency_id)
  LEFT JOIN contacts C                    ON (A.id = C.agency_id)
 WHERE O.hq = '1'
   AND A.id IN (
       SELECT agency_id FROM agencies_industries WHERE 0 = 1 OR (<criteria for industries>)
       UNION SELECT agency_id FROM agencies_professions WHERE 0 = 1 OR (<criteria for professions>)
       UNION SELECT agency_id FROM agencies_sectors WHERE 0 = 1 OR (<criteria for sectors>)
       UNION SELECT agency_id FROM agencies_seniorities WHERE 0 = 1 OR (<criteria for seniorities>)
       UNION SELECT agency_id FROM agencies_zones WHERE 0 = 1 OR (<criteria for zones>)
       UNION SELECT agency_id FROM agencies_countries WHERE 0 = 1 OR (<criteria for countries>)
       UNION SELECT agency_id FROM agencies_regions WHERE 0 = 1 OR (<criteria for regions>)
       UNION SELECT agency_id FROM agencies_cities WHERE 0 = 1 OR (<criteria for cities>)
       UNION SELECT agency_id FROM agencies_specialisms WHERE 0 = 1 OR (<criteria for specialisms>)
       UNION SELECT agency_id FROM agencies_sub_specialisms WHERE 0 = 1 OR (<criteria for sub_specialisms>));