Optimizing JOIN and WHERE operation in SQL query

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

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