0

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.

7
  • SELECT market_place.COUNTRY ,moderation.flag_status does not make sense -- both of those are constants given in WHERE/ON clauses. Let's see the real query; it probably has a different EXPLAIN plan.
    – Rick James
    Aug 7, 2022 at 3:12
  • @RickJames, the selection is generic to simplify and keep the question brief n relevant
    – Dennisrec
    Aug 7, 2022 at 3:37
  • Why are you INNER JOINing a subquery instead of directly to the moderation table?
    – J.D.
    Aug 7, 2022 at 3:53
  • @J.D., point me to where the sub-query is. Also, it would be awesome if you provide a working example based on my schema
    – Dennisrec
    Aug 7, 2022 at 4:04
  • The plan is doing pretty much what you’ve asked it to do with your hinting. The problem is that the data distribution in your tables means that this plan takes a long time to find the 25 rows it wants (from your description it sounds like actually it never finds that many rows so ends up having to loop over every post from that country). Your driving filter is presumably actually the flag_status=1 filter, looping through these rows in moderation to check the country could be more efficient. You might need statistics that tell MySQL about the usefulness of this filter Aug 7, 2022 at 6:00

1 Answer 1

0

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!

7
  • That would seem so obvious right.? Well, again the schema you're seeing is really simplified and the moderation table holds more data than just the country code
    – Dennisrec
    Aug 7, 2022 at 3:43
  • I have also tried what you're suggesting. It ain't working. It only works if I remove one condition from the on clause; but then, this defeats the purpose entirely
    – Dennisrec
    Aug 7, 2022 at 3:47
  • You can use the schema I provided and generate 15m generic records and see what I mean.
    – Dennisrec
    Aug 7, 2022 at 3:50
  • @Dennisrec - Notice how the EXPLAIN says "Using index". This means that the index is "covering". Does it say that for the 'real' query against the 'real' schema? If not, then you have over-simplified things. It is usually OK to leave things out of the list of things being SELECTed, but the rest of the query should be left intact.
    – Rick James
    Aug 7, 2022 at 6:04
  • I've left nothing out. The result of the explain is exactly what I've posted there. It's also worth mentioning that the query is way faster when records are under 100,000 but since I generated 15million generic records, it's incredibly slow even if it show as using indexes
    – Dennisrec
    Aug 7, 2022 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.