Solve performance issues for MYSQL query

skyler
September 30, 2022
71 views
1 vote
2 Answers

I have a query. It has an index but I’m not sure why it’s taking time for fewer rows. Can anyone please suggest to improving that query performance?

SELECT 
  up.id post_id, 
  c.id community_id, 
  SUBSTRING(up.description, 1, 30) post_description, 
  c.name community_name, 
  c.is_index is_index, 
  c.slug slug, 
  p.last_modified_on last_modified_on, 
  c.is_shop is_shop 
FROM 
  user_post up 
  INNER JOIN community c ON up.community_id = c.id 
  and c.is_index = 1 
  inner join participant pa on c.participant_id = pa.id 
  and pa.is_active = 1 
  inner join participating_entity p on up.participating_entity_id = p.id 
  left join indexed_post ip on ip.user_post_id = up.id 
  and ip.is_active = 1 
WHERE 
  p.is_active = 1 
  and up.is_spam_post = 0 
  and ip.id is null 
  and LENGTH(up.description) >= 20 
order by 
  up.id 
limit 
  358223, 
  5000G

row scans:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
   partitions: NULL
         type: ref
possible_keys: PRIMARY,fk_community_participant1_idx,idx_is_index_participant_id
          key: idx_is_index_participant_id
      key_len: 2
          ref: const
         rows: 6702
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pa
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: she.c.participant_id
         rows: 1
     filtered: 10.00
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: up
   partitions: NULL
         type: ref
possible_keys: fk_user_post_community1_idx,fk_user_post_entity1_idx,is_spam_post,idx_is_spam_post,idx_community_id_participating_entity_id_is_spam_post,idx_participating_entity_id_community_id
          key: idx_community_id_participating_entity_id_is_spam_post
      key_len: 9
          ref: she.c.id
         rows: 338
     filtered: 50.00
        Extra: Using index condition; Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: ip
   partitions: NULL
         type: ref
possible_keys: idx_user_post_id
          key: idx_user_post_id
      key_len: 8
          ref: she.up.id
         rows: 1
     filtered: 10.00
        Extra: Using where; Not exists
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: p
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,idx_is_active
          key: PRIMARY
      key_len: 8
          ref: she.up.participating_entity_id
         rows: 1
     filtered: 50.00
        Extra: Using where

It takes more than 15 seconds. Can anyone please suggest what is the problem? Can’t it be solved by rewriting the query to increase the performance?

data output:

*************************** 5000. row ***************************
         post_id: 1788267
    community_id: 492
post_description: #none #FoodParty
  community_name: Cooking, Food, Recipes and More...
        is_index: 1
            slug: cooking-food-recipes-and-more
last_modified_on: 2020-03-09 17:48:50
         is_shop: 0
5000 rows in set (2 min 13.05 sec)

Table structure;

Table : Community

PRIMARY KEY (`id`),
  KEY `fk_community_participant1_idx` (`participant_id`),
  KEY `fk_community_community_type1_idx` (`community_type_id`),
  KEY `idx_parent_participant_id` (`parent_participant_id`),
  KEY `idx_score_latest` (`score_latest`),
  KEY `slug` (`slug`(191)),
  KEY `idx_is_index_participant_id` (`is_index`,`participant_id`),
  CONSTRAINT `_fk_community_community_type1` FOREIGN KEY (`community_type_id`) REFERENCES `community_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `_fk_community_participant1` FOREIGN KEY (`participant_id`) REFERENCES `participant` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION


Table : participant

*************************** 1. row ***************************
       Table: participant
Create Table: CREATE TABLE `participant` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `participant_type_id` bigint(20) NOT NULL,
  `crdt` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `last_modified_on` timestamp NULL DEFAULT NULL,
  `last_modified_by` bigint(20) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `is_deleted` tinyint(1) DEFAULT '0',
  `partner_id` bigint(20) DEFAULT NULL,
  `sheroes_deep_link` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_participant_participation_type1_idx` (`participant_type_id`),
  KEY `idx_participant_last_modified_on` (`last_modified_on`),
  KEY `idx_participant_crdt` (`crdt`),
  KEY `sheroes_deep_link` (`sheroes_deep_link`(191)),
  CONSTRAINT `fk_participant_participation_type1` FOREIGN KEY (`participant_type_id`) REFERENCES `participant_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

Table: participating_entity

PRIMARY KEY (`id`),
  KEY `fk_entity_entity_type_idx` (`entity_type_id`),
  KEY `idx_participating_entity_last_modified_on` (`last_modified_on`),
  KEY `created_by` (`created_by`),
  KEY `fk_partner_id_idx` (`partner_id`),
  KEY `crdt` (`crdt`),
  KEY `idx_category` (`category`),
  KEY `idx_created_by` (`created_by`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `fk_entity_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `entity_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_partner_id` FOREIGN KEY (`partner_id`) REFERENCES `api_consumer_partner` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

Table : indexed_post

*************************** 1. row ***************************
       Table: indexed_post
Create Table: CREATE TABLE `indexed_post` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_post_id` bigint(20) NOT NULL,
  `is_active` tinyint(1) DEFAULT '0',
  `crdt` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `last_modified_by` bigint(20) DEFAULT NULL,
  `last_modified_on` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_post_id` (`user_post_id`)

Table : user_post

PRIMARY KEY (`id`),
  KEY `fk_user_post_users1_idx` (`users_id`),
  KEY `fk_user_post_community1_idx` (`community_id`),
  KEY `fk_user_post_entity1_idx` (`participating_entity_id`),
  KEY `idx_source_ent_id` (`source_entity_id`),
  KEY `idx_entity_start_dt` (`entity_start_date`),
  KEY `idx_rating_for_company` (`rating`),
  KEY `user_post_source_type` (`source_type`),
  KEY `is_spam_post` (`is_spam_post`),
  KEY `idx_is_spam_post` (`is_spam_post`),
  KEY `idx_is_theme_post` (`is_theme_post`),
  KEY `idx_meta_title` (`meta_title`),
  KEY `idx_meta_description` (`meta_description`),
  KEY `idx_recipient_id` (`recipient_id`),
  KEY `idx_title` (`title`(191)),
  KEY `idx_community_id_participating_entity_id_is_spam_post` (`community_id`,`participating_entity_id`,`is_spam_post`),
  KEY `user_post_is_recommended_IDX` (`is_recommended`),
  KEY `idx_participating_entity_id_community_id` (`participating_entity_id`,`community_id`),
  CONSTRAINT `__fk_user_post_community1` FOREIGN KEY (`community_id`) REFERENCES `community` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `__fk_user_post_entity1` FOREIGN KEY (`participating_entity_id`) REFERENCES `participating_entity` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `__fk_user_post_users1` FOREIGN KEY (`users_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

Answers

Could you try the following?

SELECT 
  up.id post_id, 
  c.id community_id, 
  SUBSTRING(up.description, 1, 30) post_description, 
  c.name community_name, 
  c.is_index is_index, 
  c.slug slug, 
  p.last_modified_on last_modified_on, 
  c.is_shop is_shop 
FROM 
  user_post up 
  INNER JOIN community c ON up.community_id = c.id AND up.is_spam_post = 0 and c.is_index = 1 AND  LENGTH(up.description) >= 20 
  inner join participant pa on c.participant_id = pa.id and pa.is_active = 1 
  inner join participating_entity p on up.participating_entity_id = p.id AND   p.is_active = 1 
  left join indexed_post ip on ip.user_post_id = up.id and ip.is_active = 1 
WHERE 
   ip.id is null 
order by 
  up.id 
limit 
  358223, 
  5000G

- RickJames
- September 30, 2022 at 6:31 pm
- 0 votes
0
Nothing can fix
```
limit  358223, 5000
```
That query must join all those tables, filter out the unnecessary rows, sort them, skip over 358223 rows and finally deliver 5000.

If you are dumping a million rows, either
- Dump them all at once, so you are not rebuilding the set over and over.
- "Remember where you left off" so you don’t have to perform OFFSET. However, because of all the complexity in the query, I doubt if this is possible.
So, in my opinion, the real cure is to rethink why you need this query.

Note that each table needs to test for is_active. This effective prevents some optimizations.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.