0

Hello to Folks out there,

I need some suggestion on How to design MySQL Table Structure for search optimization.

I am creating a Real estate website. In that I have property table and all its associated tables.

I can design my table for saving these records in two ways.

I have amenity master table

id property_name
----------------
1  Property A
2  Property B
3  Property C

Approach 1 Property Table

id property_name
----------------
1  Property A
2  Property B
3  Property C

Property_Amenities table

id p_id  amenity_id
------------------------
1  1     1       
2  1     2
3  1     3
4  1     4       
5  2     1
6  2     1

Approach 2
Property Table

id property_name  amenity_id
----------------------------
1  Property A     1,2,3,4,5
2  Property B     1,4,7,9,12  
3  Property C     3,4,7,8,9,10

Approach 1 Query : I can join tables and get the all the amenities name for a particular property. Add the required index for optimization. For A property there will be 20 amenities on averages. Suppose I have 100K property records then to get amenities for particular property, MySQL query will search in 2 millions records of Property_Amenities table.

Approach 2 Query : I can search using FIND_IN_SET or IN MySQL operator. But I was going through this search topic and it seems that this approach will be much resource intensive and cost more for same amount of data i.e 100k property records.

Any suggestion will be appropriated. What your thought on this scenario or any other approach I should follow.

Abhishek
  • 164
  • 1
  • 2
  • 10
  • Aren't some of the amenities numeric? Won't people ask for ranges? None of what is said here, nor in the "dup" query covers that situation. If you need ranges, (1) search again for Q&A that have covered it; then consider (2) starting another Q with a clearer list of attributes for the properties. – Rick James May 23 '20 at 23:58

1 Answers1

1

Use the first approach. Period. The second is wrong, wrong, wrong. Here are some reasons:

  • A SQL string should not be used to store multiple values.
  • Integers should be stored using the proper type.
  • Foreign key relationships should be declared.
  • SQL has very poor string handling methods.
  • SQL has a great way to store lists; it is called a "table" not a "string".
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    On a tachnical level: IN and text search is BRUTALLY more CPU intensive than comparing integer numbers. – TomTom May 23 '20 at 12:09