0

Below is my query:

with cte as (SELECT a.postId
 from (SELECT p.postId
       FROM join_area ja
                INNER JOIN post p USING (areaId)
       WHERE ja.belongAreaId = 29
         AND p.postType in (0, 1)
       limit 15 offset 130) a
 where exists(select 1 from join_post_cat jpc
                 where jpc.postId = a.postId
                   and jpc.categoryId IN (1, 2, 3)))
select postId,
postTitle,
postPrice,
availableFrom,
fullAddress,
floor,
room,
bath,
imageUrl
from cte
inner join post p2 using (postId);

Below is that query visual explain:

enter image description here

Now i want to apply filter for this query. So the query will look like below:

with cte as (SELECT a.postId
 from (SELECT p.postId
       FROM join_area ja
                INNER JOIN post p USING (areaId)
       WHERE ja.belongAreaId = 29
             AND p.postType in (0, 1)
         AND (p.postPrice > 0 AND p.postPrice < 10000000)
         AND p.availableFrom = '2019-06-26'
         AND (p.floor >= 0 and p.floor <= 6)
         AND (p.room >= 0 and p.room <= 20)
         AND (p.bath >= 0 and p.bath <= 10)
         AND (p.size >= 0 and p.size <= 20000)
       limit 15 offset 130) a
 where exists(select 1 from join_post_cat jpc
               where jpc.postId = a.postId
                 and jpc.categoryId IN (1, 2, 3)))
select postId,
postTitle,
postPrice,
availableFrom,
fullAddress,
floor,
room,
bath,
imageUrl
from cte
inner join post p2 using (postId);

Below is that query visual explain:

enter image description here

Both two query work good, but I'm little bit confused about second query efficiency. Do i need to add index for second query, so that it may work more fast or efficient? Now i have index on post table for (areaId, postType, postId DESC) column. Do i need to extend this index to (areaId, postType, postId DESC, postPrice, availableFrom, floor, room, bath, size) or previous index will be good?

Below is post table structure:

CREATE TABLE `post` (
  `postId` bigint(20) NOT NULL AUTO_INCREMENT,
  `userId` mediumint(8) unsigned NOT NULL,
  `areaId` smallint(5) unsigned DEFAULT NULL,
  `postTitle` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `postPrice` int(10) unsigned NOT NULL,
  `availableFrom` date DEFAULT NULL,
  `postCreateDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `postCreateIp` varbinary(16) DEFAULT NULL,
  `postUpdateDate` timestamp NULL DEFAULT NULL,
  `postUpdateIp` varbinary(16) DEFAULT NULL,
  `fullAddress` varchar(127) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `floor` tinyint(3) unsigned DEFAULT NULL,
  `room` tinyint(3) unsigned DEFAULT NULL,
  `bath` tinyint(3) unsigned DEFAULT NULL,
  `size` smallint(5) unsigned DEFAULT NULL,
  `postContactNo` varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `des` varchar(511) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `imageUrl` varchar(127) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `postType` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0>for_rent\n1>need_rent',
  PRIMARY KEY (`postId`),
  KEY `post_userId_postCreateDate_index` (`userId`,`postCreateDate`),
  KEY `post_areaId_postType_postId_index` (`areaId`,`postType`,`postId` DESC),
  CONSTRAINT `post_join_area_areaId_fk` FOREIGN KEY (`areaId`) REFERENCES `join_area` (`areaId`),
  CONSTRAINT `post_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=20004 DEFAULT CHARSET=utf8

Below is join_area table structure:

CREATE TABLE `join_area` (
  `areaId` smallint(5) unsigned NOT NULL,
  `belongAreaId` smallint(5) unsigned NOT NULL,
  UNIQUE KEY `join_area_pk` (`belongAreaId`,`areaId`),
  KEY `join_area_areaId_index` (`areaId`),
  CONSTRAINT `join_area_area_areaId_fk` FOREIGN KEY (`areaId`) REFERENCES `area` (`areaId`),
  CONSTRAINT `join_area_area_areaId_fk_2` FOREIGN KEY (`belongAreaId`) REFERENCES `area` (`areaId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Below is join_post_cat table structure:

CREATE TABLE `join_post_cat` (
  `postId` bigint(20) NOT NULL,
  `categoryId` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`categoryId`,`postId`),
  KEY `join_post_cat_postId_categoryId_index` (`postId`,`categoryId`),
  CONSTRAINT `join_post_cat_categories_categoryId_fk` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`),
  CONSTRAINT `join_post_cat_post_postId_fk` FOREIGN KEY (`postId`) REFERENCES `post` (`postId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

..

CREATE TABLE area (
    areaId smallint(5) unsigned NOT NULL AUTO_INCREMENT, 
    mainAreaId smallint(5) unsigned NOT NULL, 
    areaName varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
    areaNameBn varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 
    PRIMARY KEY (areaId), 
    KEY area_mainAreaId_index (mainAreaId), 
    KEY area_areaNameBn_index (areaNameBn), 
    KEY area_areaName_index (areaName)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8
  • Here is an explanation dev.mysql.com/doc/refman/8.0/en/… – Kondybas Jul 25 at 5:59
  • @Kondybas thanks for the link, there explain how to optimize range index, and after understand it, i may able to apply it with my query but still my question is do i need to create index for all the where clause rows? – Rhidoy Jul 25 at 9:47
  • Suggested index - (areaId, postId , availableFrom) – JYOTI RAJAI Jul 25 at 13:32
  • @JYOTIRAJAI thanks but why not others column? is this for =? so does it means i don't need to use index if i use range (<= =>)? but Kondybas suggested link using indexes for range. – Rhidoy Jul 25 at 13:59
  • Need to see the other CREATE TABLEs. – Rick James Jul 26 at 3:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.