MySQL - Optimize select with join to a large table

Question

I have done some digging and found exactly no help at all for questions similar to this one. Basically I have two tables where one stores user posts and the other holds the moderation of the records of in table a "marketplace".

Table schema A(marketplace)

-- has 15 million records
create table marketPlace(
    ID          bigint auto_increment primary key,
    USER_ID     bigint                            not null,
    TITLE       varchar(60)                       not null,
    DESCRIPTION varchar(1000) collate utf8mb4_bin not null,
    COUNTRY     varchar(2)                        not null comment 'Country iso_code.',
    VIEWS       bigint default 0                  not null,
    DATE        bigint                            not null
);
create index COUNTRY on marketPlace (COUNTRY);
create index DATE on marketPlace (DATE);
create index ID_and_country_index on marketPlace (ID, COUNTRY);

Table schema B(moderation)

-- also has 15 million records
create table moderation(
    ID          bigint auto_increment primary key,
    adID        bigint not null comment 'The ID of the post being flagged',
    mode_flags  json   not null comment 'Flags added by approved moderators',
    flag_status bigint as (json_unquote(json_extract(`mode_flags`, '$.mod.flag'))) stored comment 'Post moderation status',
    date        bigint not null
);
create index composite_index on moderation (adID, flag_status);
create index flag_status on moderation (flag_status);
create index modID on moderation (modID);

And am using the following select with join query that's taking almost 2 minutes.

SELECT
    market_place.COUNTRY
    ,moderation.flag_status

FROM marketPlace market_place
    #use index (COUNTRY)

     INNER JOIN (
        SELECT adID, flag_status
        FROM moderation #use INDEX(composite_index)
    )moderation ON(moderation.flag_status = 1 AND market_place.ID = moderation.adID)
WHERE market_place.COUNTRY='GB'
LIMIT 25;

As you can see in the query above, I have also tried to force MySQL to use indexes and I still get results after 120 seconds or so. I did run a quick explain on the query and showed that indexes are being used but somehow still takes way too long to get back just a few records.

Explain result:

#   id  select_type  table           partitions     type        possible_keys                       key         key_len    ref                              rows        filtered    Extra
1   1   SIMPLE       market_place    NULL           ref         PRIMARY,COUNTRY                     COUNTRY     10         const                            7143700     100         Using index
2   1   SIMPLE       moderation      NULL           eq_ref      adID,flag_status,composite_index    adID        8          main_database.market_place.ID    1           50          Using where

Is there a way to optimize and speed up the query above.? Any help will gladly be appreciated. Thank you.

Answer 1

Countries have standard 2-letter abbreviations. Use them rather than having to look them up. This would eliminate the JOIN and probably make the query faster.

If you need other columns from either table, so state!!

This might help:

INDEX(flag_status,  adID)

The convention for ON versus WHERE is to put the conditions that define the relationship in ON; and put "filtering" clauses in WHERE. (For INNER JOIN, they are equivalent, but it helps humans to make the distinction.)

Use COUNTRY as the PRIMARY KEY, not the artificial BIGINT that is 4 times as large!