Below is an sample query. On a glimpse I’m trying to figure out how to optimize this query better, I’m trying to reduce the OR operator at the WHERE option at the bottom but I haven’t figure out how. Any advice or other optimization I can do to this query?
SELECT Positions.id AS `Positions__id`,
Positions.name AS `Positions__name`,
Positions.media_id AS `Positions__media_id`,
Positions.position_category_id AS `Positions__position_category_id`,
Positions.position_type_id AS `Positions__position_type_id`,
Positions.description AS `Positions__description`,
Positions.detail AS `Positions__detail`,
Positions.business_skill AS `Positions__business_skill`,
Positions.knowledge AS `Positions__knowledge`,
Positions.location AS `Positions__location`,
Positions.activity AS `Positions__activity`,
Positions.academic_degree_doctor AS `Positions__academic_degree_doctor`,
Positions.academic_degree_master AS `Positions__academic_degree_master`,
Positions.academic_degree_professional AS `Positions__academic_degree_professional`,
Positions.academic_degree_bachelor AS `Positions__academic_degree_bachelor`,
Positions.salary_statistic_group AS `Positions__salary_statistic_group`,
Positions.salary_range_first_year AS `Positions__salary_range_first_year`,
Positions.salary_range_average AS `Positions__salary_range_average`,
Positions.salary_range_remarks AS `Positions__salary_range_remarks`,
Positions.restriction AS `Positions__restriction`,
Positions.estimated_total_workers AS `Positions__estimated_total_workers`,
Positions.remarks AS `Positions__remarks`,
Positions.url AS `Positions__url`,
Positions.seo_description AS `Positions__seo_description`,
Positions.seo_keywords AS `Positions__seo_keywords`,
Positions.sort_order AS `Positions__sort_order`,
Positions.publish_status AS `Positions__publish_status`,
Positions.version AS `Positions__version`,
Positions.created_by AS `Positions__created_by`,
Positions.created AS `Positions__created`,
Positions.modified AS `Positions__modified`,
Positions.deleted AS `Positions__deleted`,
PositionCategories.id AS `PositionCategories__id`,
PositionCategories.name AS `PositionCategories__name`,
PositionCategories.sort_order AS `PositionCategories__sort_order`,
PositionCategories.created_by AS `PositionTypes__created_by`,
PositionTypes.created AS `PositionTypes__created`,
PositionTypes.modified AS `PositionTypes__modified`,
PositionTypes.deleted AS `PositionTypes__deleted`
FROM positions Positions
LEFT JOIN positions_personalities PositionsPersonalities
ON Positions.id = (PositionsPersonalities.position_id)
LEFT JOIN personalities Personalities
ON (Personalities.id = (PositionsPersonalities.personality_id)
AND (Personalities.deleted) IS NULL)
LEFT JOIN positions_practical_skills PositionsPracticalSkills
ON Positions.id = (PositionsPracticalSkills.position_id)
LEFT JOIN practical_skills PracticalSkills
ON (PracticalSkills.id = (PositionsPracticalSkills.practical_skill_id)
AND (PracticalSkills.deleted) IS NULL)
LEFT JOIN positions_basic_abilities PositionsBasicAbilities
ON Positions.id = (PositionsBasicAbilities.position_id)
LEFT JOIN basic_abilities BasicAbilities
ON (BasicAbilities.id = (PositionsBasicAbilities.basic_ability_id)
AND (BasicAbilities.deleted) IS NULL)
LEFT JOIN positions_tools PositionsTools
ON Positions.id = (PositionsTools.position_id)
LEFT JOIN affiliates Tools
ON (Tools.type = 1 AND Tools.id = (PositionsTools.affiliate_id)
AND (Tools.deleted) IS NULL)
LEFT JOIN positions_career_paths PositionsCareerPaths
ON Positions.id = (PositionsCareerPaths.position_id)
LEFT JOIN affiliates CareerPaths
ON (CareerPaths.type = 3
AND CareerPaths.id = (PositionsCareerPaths.affiliate_id)
AND (CareerPaths.deleted) IS NULL)
LEFT JOIN positions_rec_qualifications PositionsRecQualifications
ON Positions.id = (PositionsRecQualifications.position_id)
LEFT JOIN affiliates RecQualifications
ON (RecQualifications.type = 2 AND RecQualifications.id = (PositionsRecQualifications.affiliate_id)
AND (RecQualifications.deleted) IS NULL)
LEFT JOIN positions_req_qualifications PositionsReqQualifications
ON Positions.id = (PositionsReqQualifications.position_id)
LEFT JOIN affiliates ReqQualifications
ON (ReqQualifications.type = 2 AND ReqQualifications.id = (PositionsReqQualifications.affiliate_id)
AND (ReqQualifications.deleted) IS NULL)
INNER JOIN position_categories PositionCategories
ON (PositionCategories.id = (Positions.position_category_id)
AND (PositionCategories.deleted) IS NULL)
INNER JOIN position_types PositionTypes
ON (PositionTypes.id = (Positions.position_type_id)
AND (PositionTypes.deleted) IS NULL)
WHERE ((PositionCategories.name LIKE '%Software Engineer%'
OR PositionTypes.name LIKE '%Software Engineer%'
OR Positions.name LIKE '%Software Engineer%'
OR Positions.description LIKE '%Software Engineer%'
OR Positions.detail LIKE '%Software Engineer%'
OR Positions.business_skill LIKE '%Software Engineer%'
OR Positions.knowledge LIKE '%Software Engineer%'
OR Positions.location LIKE '%Software Engineer%'
OR Positions.activity LIKE '%Software Engineer%'
OR Positions.salary_statistic_group LIKE '%Software Engineer%'
OR Positions.salary_range_remarks LIKE '%Software Engineer%'
OR Positions.restriction LIKE '%Software Engineer%'
OR Positions.remarks LIKE '%Software Engineer%'
OR Personalities.name LIKE '%Software Engineer%'
OR PracticalSkills.name LIKE '%Software Engineer%'
OR BasicAbilities.name LIKE '%Software Engineer%'
OR Tools.name LIKE '%Software Engineer%'
OR CareerPaths.name LIKE '%Software Engineer%'
OR RecQualifications.name LIKE '%Software Engineer%'
OR ReqQualifications.name LIKE '%Software Engineer%')
AND publish_status = 1
AND (Positions.deleted) IS NULL) GROUP BY Positions.id
ORDER BY Positions.sort_order desc,
Positions.id DESC LIMIT 50 OFFSET 0
Advertisement
Answer
I think creating full text index will be better option for you. If you need further information regarding this please visit:
https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html