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.
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 differentEXPLAIN
plan.INNER JOIN
ing a subquery instead of directly to themoderation
table?flag_status=1
filter, looping through these rows inmoderation
to check thecountry
could be more efficient. You might need statistics that tell MySQL about the usefulness of this filter