0

Below is my 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(120) 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) unsigned 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

I want to add search functionality for this table. There will be below conditions:

  1. I may search by text which will match with rows postTitle and des.
  2. I may also filter those result by many conditions for rows areaId, postType postPrice, availableFrom, floor, room, bath, size.

Below is an example:

select *
from post
WHERE areaId = 29
  AND postType in (0, 1)
  AND (postPrice > 0 AND postPrice < 10000000)
  AND availableFrom = '2019-06-26'
  AND (floor >= 0 and floor <= 6)
  AND (room >= 0 and room <= 20)
  AND (bath >= 0 and bath <= 10)
  AND (size >= 0 and size <= 20000);

Now I'm not sure how can I implement that efficiently. I can think below ways:

  1. First I will query for data which match against postTitle and des rows, this query will use MySQL full text index which I will create on postTitle and des rows. Then i will apply filter on those result for areaId, postType postPrice, availableFrom, floor, room, bath, size rows but this will not cover any index because this will use temp table.
  2. Second, I may write query by combining all conditions like below:

    select *
    from post
    WHERE (postTitle like '%post%' or des like '%test%')
      AND areaId = 29
      AND postType in (0, 1)
      AND (postPrice > 0 AND postPrice < 10000000)
      AND availableFrom = '2019-06-26'
      AND (floor >= 0 and floor <= 6)
      AND (room >= 0 and room <= 20)
      AND (bath >= 0 and bath <= 10)
      AND (size >= 0 and size <= 20000);
    

But I'm not sure how to make this query efficient. How to create index for this query. Please suggeset me how can I design this search functionality or what do I need to do to make this query efficient?

1

Use Fulltext:

FULLTEXT(postTitle, des)

and

AND MATCH(postTitle, des) AGAINST("+post +test" IN BOOLEAN MODE)

Construct, and optionally leave out, any tests that are not requested. That is, don't include this line if there are only 20 rooms:

AND (room >= 0 and room <= 20)

Don't bother will bath >= 0 since it is UNSIGNED.

Gather typial queries. Either by asking 'users' what they will ask about or by collecting queries on the fly. Then construct the 'optimal' indexes.

But, first, note that if the MATCH clause is included, it will be the only index used. That is, for cases where the user is not testing on postTitle and des, then we can look at indexing.

Clarification: It will use the FT index first, then filter on any other WHERE clause, but without the benefit of INDEXes. The assumption is that FT filters down to very few rows, and the subsequent filtering is not a problem.

The only two useful columns in the above example are those with =. So:

INDEX(areaId, availableFrom)

But, after looking a user activity, you may find others.

See also Elasticsearch.

  • 'Elasticsearch' that's the word i'm looking for to understand search engine thanks for mentioning it, i will study on that. but now do my problem need to implement Elasticsearch? I'm not sure. From your solution it's clear that i may only used either text index or other index not both at a time, but i need to use both at a time. The reason is suppose user search by text first, than s/he want to filer the result, according areaId, postType postPrice, room, bath, size etc, at this time i have to add text search along with filtered rows. that's where I got hang. How can i handle this? – Rhidoy Jul 26 at 6:59
  • @Rhidoy - I added Clarification. The SELECT can include both text criteria and numeric criteria. Both will be honored. – Rick James Jul 26 at 18:11
  • yeah i got it, one more clarification. as tinyint unsigned row may contain 0-255, so if i create index with those columns and in query add where clause as (row>255 and row<0 ) than is it gonna skip index checking? because this condition is not valid. by doing so i can skip query writing on fly. – Rhidoy Jul 26 at 18:57
  • @Rhidoy - No, I don't think the Optimizer bothers with impossible cases. Anyway, you would want those in OR, not AND. – Rick James Jul 26 at 19:54

Your Answer

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

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