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.
# 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.