Optimization scheme of label screening

as shown in figure

clipboard.png

this kind of tag filtering can be done directly by sql if it is taken directly from the database, but it is not ideal. If the listing of each tag has a list of Redis, for example: the list of
area (Chaoyang District)-1 235,456, 1211983
area (80 square meters)-21, 243, 1456, 1211983442
, is there any good optimization scheme

?

I thought of two schemes:
1 list of each tag, a group of 30, list of
area (Chaoyang District)-list of group 1-1 br > area (Chaoyang District)-list of 23 br > area (Chaoyang District)-list of group 2-
area (80 square meters)-list of group 1-21, 243 br > 1456, 1211983442
area (80 square meters)-group 2-
if 10 pieces of data are displayed per page, The user chooses a house of 80 square meters in Chaoyang District. I will take the intersection of group 1 of the area list and group 1 of the area above. If the result is enough, then meet the demand. If there are not 10, then take the second group of information until 10 pieces of information
are displayed. But there is a big problem: the more paging, the greater the amount of calculation, and the larger the amount of data. The effect is definitely not ideal
and it is difficult to figure out the total number of entries (although the total records are generally not displayed now). Even if the tags are not grouped, if the list in Chaoyang District has 1000W, it seems awkward to throw it into memory when you pick up the intersection.

2 schedule tasks with sql+, store the results of each possibility in Redis, but if there are a lot of tags, many sql, will feel that the loss outweighs the gain

I would like to ask you, is there any good solution to this kind of problem?
Thank you all


I think sql is a good way to classify houses by label (one field for each type), and a sql (select * where astatx or breadx or centerx) will come out. Once the index is built, there will be basically no performance problems. If you are still worried, redis will cache the results and multiply them with only one or two hundred results.


for this requirement, it is not recommended to query directly using sql.
is generally implemented using a "full-text search engine". PHP has sphinx, that can use sphinx to implement a search middle-tier service (middleware search service), which is called by the business to achieve fast retrieval.

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1e94b00-45f7a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1e94b00-45f7a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?