Optimizing complex sql query MySql 8.0

Transact-SQL
1

Hello everyone I have a complex query that creates unique feed of tenders for each user based on his/her interests, areas of activity, keywords, and budget ranges. Now I have 1M+ tenders and this query takes 30secods+ to execute how can it be rewritten in different more optimized manner?

SELECT
    *
FROM
    `tenders`
WHERE
    (
        `activity_field_id` IN(
        SELECT
            `activity_field_id`
        FROM
            `activity_field_user`
        WHERE
            `user_id` = '1'
    ) OR EXISTS(
    SELECT
        *
    FROM
        `lots`
    WHERE
        `tenders`.`id` = `lots`.`tender_id` AND EXISTS(
        SELECT
            *
        FROM
            `lot_items`
        WHERE
            `lots`.`id` = `lot_items`.`lot_id` AND `code_id` IN(
            SELECT
                `id`
            FROM
                `codes`
            WHERE
                `activity_field_id` IN(
                SELECT
                    `activity_field_id`
                FROM
                    `activity_field_user`
                WHERE
                    `user_id` = '1'
            )
        )
    )
) OR EXISTS(
    SELECT
        *
    FROM
        `activity_fields`
    INNER JOIN `activity_field_tender` ON `activity_fields`.`id` = `activity_field_tender`.`activity_field_id`
    WHERE
        `tenders`.`id` = `activity_field_tender`.`tender_id` AND `activity_fields`.`id` IN(
        SELECT
            `activity_field_id`
        FROM
            `activity_field_user`
        WHERE
            `user_id` = '1'
    )
) OR `organization_id` IN(
    SELECT
        `organization_id`
    FROM
        `organization_user`
    WHERE
        `user_id` = '1'
) OR(
    `tenders`.`title` LIKE '%оборудование%'
) AND `public_time` BETWEEN '2022-04-03 00:00:00' AND '2022-05-03 23:59:59'
    ) AND `canceled_at` IS NULL AND `protocol_created_at` IS NULL AND `end_time` >= '2022-05-03 15:47:11' AND `public_time` >= '2022-04-03 00:00:00' AND `public_time` < '2022-05-03 23:59:59' AND NOT EXISTS(
    SELECT
        *
    FROM
        `black_lists`
    WHERE
        `tenders`.`id` = `black_lists`.`tender_id` AND `user_id` = '1'
) AND NOT EXISTS(
    SELECT
        *
    FROM
        `tender_works`
    WHERE
        `tenders`.`id` = `tender_works`.`tender_id` AND `user_id` = '1'
)
ORDER BY
    `public_time` ASC
LIMIT 20 OFFSET 0;
2

FYI, SQLTeam is a Microsoft SQL Server-focused site. Our MySQL knowledge may not be the best.

What does the EXPLAIN PLAN look like for this query? Generally you'd want to avoid scanning tables and prefer accessing indexes. If you see scans on tables where you only need 1 or 2 columns, you will probably want to create an index on those column(s). Any columns that have WHERE conditions or JOIN conditions would probably benefit from being indexed.

Additionally, the places where you use IN(SELECT ... FROM ....) might optimize better if you used a JOIN instead. Especially where you have nested IN() clauses. They could also be rewritten as EXISTS, like you have later in the query.

There's some more options that might help but it's probably best to post the EXPLAIN plan and work on some of the things I already mentioned first.

3

image
image1351×402 46.2 KB

Here is the output of explain statement

4

Can you post it as text? The columns cut off on the right might have relevant info.

5

Here it is:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 PRIMARY tenders NULL ALL NULL NULL NULL NULL 138680 0.04 Using where; Using filesort
1 PRIMARY NULL eq_ref <auto_distinct_key> <auto_distinct_key> 9 tsapi.tenders.id 1 100.00 Using where; Not exists
1 PRIMARY NULL eq_ref <auto_distinct_key> <auto_distinct_key> 9 tsapi.tenders.id 1 100.00 Using where; Not exists
10 MATERIALIZED black_lists NULL ref black_lists_user_id_foreign,black_lists_tender_id_foreign black_lists_user_id_foreign 8 const 14 100.00 Using index condition
11 MATERIALIZED tender_works NULL ref tender_works_tender_id_foreign,tender_works_user_id_foreign tender_works_user_id_foreign 9 const 6 100.00 Using index condition
9 SUBQUERY organization_user NULL ref organization_user_organization_id_foreign,organization_user_user_id_foreign organization_user_user_id_foreign 8 const 3 100.00 Using index condition
7 DEPENDENT SUBQUERY activity_field_tender NULL ref activity_field_tender_activity_field_id_foreign,activity_field_tender_tender_id_foreign activity_field_tender_tender_id_foreign 8 tsapi.tenders.id 1 100.00 NULL
7 DEPENDENT SUBQUERY activity_field_user NULL ref activity_field_user_activity_field_id_foreign,activity_field_user_user_id_foreign activity_field_user_activity_field_id_foreign 8 tsapi.activity_field_tender.activity_field_id 14 4.70 Using where; LooseScan
7 DEPENDENT SUBQUERY activity_fields NULL eq_ref PRIMARY PRIMARY 8 tsapi.activity_field_tender.activity_field_id 1 100.00 Using index
3 DEPENDENT SUBQUERY lots NULL ref PRIMARY,lots_tender_id_foreign lots_tender_id_foreign 8 tsapi.tenders.id 6 100.00 Using index
3 DEPENDENT SUBQUERY lot_items NULL ref lot_items_code_id_foreign,lot_items_lot_id_foreign lot_items_lot_id_foreign 8 tsapi.lots.id 3 100.00 NULL
3 DEPENDENT SUBQUERY codes NULL eq_ref PRIMARY,codes_activity_field_id_foreign PRIMARY 8 tsapi.lot_items.code_id 1 100.00 Using where
3 DEPENDENT SUBQUERY activity_field_user NULL ref activity_field_user_activity_field_id_foreign,activity_field_user_user_id_foreign activity_field_user_activity_field_id_foreign 8 tsapi.codes.activity_field_id 14 4.70 Using where; FirstMatch(lots)
2 SUBQUERY activity_field_user NULL ref activity_field_user_activity_field_id_foreign,activity_field_user_user_id_foreign activity_field_user_user_id_foreign 8 const 21 100.00 Using index condition