#mysql #sql #cakephp
#mysql #sql #cakephp
Вопрос:
У меня есть таблица агентств, которая имеет отношения «многие ко многим» с зонами, странами, регионами, городами, отраслями, профессиями, секторами, специализациями и подспециальностями.
Существует также связь «один ко многим» с офисами и контактами.
По сути, я хотел создать таблицу результатов, содержащую конкретные записи из этих таблиц.
Я создал индексы для каждого внешнего ключа в таблицах. Это мой запрос:
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
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
При его выполнении сайт загружается и загружается, и через 5 минут он выходит из строя. Я понятия не имею, почему это занимает так много времени. В процессах в phpMyAdmin написано «копирование в таблицу tmp». После завершения этого процесса загрузка сайта завершается с ошибкой. Иногда после выполнения этого запроса весь phpMyAdmin не работает.
У вас есть какие-нибудь идеи, как заставить его работать нормально?
PS
В базе данных всего около 20 или менее строк в каждой таблице. Кроме того, при предоставлении предложения WHERE в конце с фиктивными данными запрос выполняется через 10 секунд, не показывая никаких результатов. Когда я предоставляю данные, соответствующие только одному результату, он выполняется примерно за 20 секунд.
Обновить
Хорошо, извините, я принял ответ слишком быстро, потому что я не объяснил точно, почему мне нужно соединить слева те таблицы, которые не использовались в моем предыдущем примере.
Дело в том, что мне нужно, чтобы они тоже использовались, потому что все это поисковая система — кто-то хотел бы найти агентство, где его зона — Африка — в результатах оно не отображается, но его нужно выбрать.
Когда я изменил свой запрос на этот:
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
Он все еще загружается до сбоя сервера.
В процессах теперь написано «Отправка данных»
Комментарии:
1. Все эти подвыборки не имеют никакой цели и сбивают с толку оптимизатор запросов. Смотрите Ответ redfilter.
2. Если вы измените начало вашего запроса на
select count(*) from ...
. Какой результат вы получаете?3. @Karolis Я ничего не понимаю — он загружается, загружается и загружается…
Ответ №1:
С вашим запросом есть две проблемы. 1) Есть много ненужных подвыборок. 2) Вам ОСТАЕТСЯ присоединиться к таблицам, из которых вы никогда не выбираете данные.
Попробуйте это вместо этого:
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
Комментарии:
1. @Piotr Я вижу, вы удалили Accept, есть ли проблема с моим ответом?
2. @RedFilter Я пытался запустить sql-запрос с помощью консоли phpMyAdmin и с помощью запроса через cakephp framework — это не работает в любом случае
3. Проблема может заключаться в том, что большая часть данных имеет то же
agency_id
самое . Тогда этот запрос может привести к очень большому набору результатов. 20 ^ 13 = 819200000000000004. Итак, каково решение? Должен быть способ решить эту проблему.
5. @PiotrChabros Если мое предположение было правильным, вам нужно уменьшить свой набор результатов с помощью фильтров. Вы собираетесь показывать миллионы данных пользователю? Вы должны запрашивать только те данные, которые вы собираетесь показать пользователю, не более.
Ответ №2:
После некоторого обсуждения я думаю, что это может сработать:
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>);
Вам нужно будет заменить каждый из них на ваши критерии поиска.
Если вы хотите динамически подключать критерии, вы можете использовать фиктивные ложные условия или использовать подключенные критерии, например:
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>));
Комментарии:
1. Ваш код технически такой же, как тот, который я вставил в свою обновленную версию, он не работает … 🙁
2. Сколько данных у вас есть в каждой из таблиц, перечисленных в запросе? Кроме того, как индексируются ваши таблицы?
3. Не более 20 строк в каждой таблице. Я просто нажал «индекс» в каждом внешнем ключе в таблицах.
4. ОК… Я знаю, что вам нужны все эти связанные таблицы для вашего поиска, но в качестве процесса ограничения, что произойдет, если вы удалите их все, кроме агентств, офисов и контактов? Тогда запрос работает? Насколько это быстро?
5. Затем добавьте их обратно, один за другим … проверяя каждый раз. Приводит ли одна из таблиц к сбою … или она постепенно становится все медленнее и медленнее с каждой дополнительной таблицей?