The optimization process and recommendations:

1. Avoid OFFSET In LIMIT Clause (query line: 82): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
2. Avoid Selecting Unnecessary Columns (query line: 5): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
3. Avoid Subqueries (query line: 4): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
4. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
5. Mixed Order By Directions Prevents Index Use (query line: 81): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
6. Replace In Subquery With Correlated Exists (modified query below): In many cases, an EXISTS subquery with a correlated condition will perform better than a non correlated IN subquery.
7. Replace Join With Exists To Avoid Redundant Grouping (modified query below): When a joined table isn’t used anywhere other than in the WHERE clause, it's equivalent to an EXISTS subquery, which often performs better. In cases where the DISTINCT or GROUP BY clause contains only columns from the Primary key, they can be removed to further improve performance, as after this transformation, they are redundant.

Optimal indexes for this query:

ALTER TABLE `contacts` ADD INDEX `contacts_idx_id` (`id`);
ALTER TABLE `contacts2tags` ADD INDEX `contacts2tags_idx_contactid` (`contactid`);

The optimized query:

SELECT
        * 
    FROM
        (SELECT
            *,
            IF(contacts.first_name LIKE 'Partha S' 
            OR contacts.last_name LIKE 'Partha S' 
            OR contacts.phone_number LIKE 'Partha S' 
            OR contacts.mobile_number LIKE 'Partha S' 
            OR contacts.email_address LIKE 'Partha S' 
            OR contacts.address LIKE 'Partha S' 
            OR contacts.organization LIKE 'Partha S' 
            OR contacts.other LIKE 'Partha S' 
            OR contacts.sector LIKE 'Partha S' 
            OR contacts.designation LIKE 'Partha S' 
            OR concat(contacts.first_name,
            ' ',
            contacts.last_name) LIKE 'Partha S' 
            OR concat(contacts.last_name,
            ' ',
            contacts.first_name) LIKE 'Partha S',
            1,
            0) AS exact,
            IF((contacts.first_name LIKE '%Partha%' 
            OR contacts.last_name LIKE '%Partha%' 
            OR contacts.phone_number LIKE '%Partha%' 
            OR contacts.mobile_number LIKE '%Partha%' 
            OR contacts.email_address LIKE '%Partha%' 
            OR contacts.address LIKE '%Partha%' 
            OR contacts.organization LIKE '%Partha%' 
            OR contacts.other LIKE '%Partha%' 
            OR contacts.sector LIKE '%Partha%' 
            OR contacts.designation LIKE '%Partha%') 
            AND (contacts.first_name LIKE '%S%' 
            OR contacts.last_name LIKE '%S%' 
            OR contacts.phone_number LIKE '%S%' 
            OR contacts.mobile_number LIKE '%S%' 
            OR contacts.email_address LIKE '%S%' 
            OR contacts.address LIKE '%S%' 
            OR contacts.organization LIKE '%S%' 
            OR contacts.other LIKE '%S%' 
            OR contacts.sector LIKE '%S%' 
            OR contacts.designation LIKE '%S%'),
            1,
            0) AS normal 
        FROM
            contacts 
        WHERE
            EXISTS (
                SELECT
                    DISTINCT 1 
                FROM
                    contacts AS contacts1 
                WHERE
                    (
                        (
                            tagid IN (
                                178
                            )
                        ) 
                        AND (
                            contacts.id = contacts1.id
                        )
                    ) 
                    AND (
                        EXISTS (
                            SELECT
                                1 
                            FROM
                                contacts2tags 
                            WHERE
                                contacts1.id = contacts2tags.contactid
                        )
                    )
                )
        ) d 
    WHERE
        exact = 1 
        OR normal = 1 
    ORDER BY
        exact DESC,
        d.last_name ASC LIMIT 0,
        20