2

Recently I'm working with RETS, So I have to download all data and save it to our webserver. I'm ussing MySQL and PHP.

I saw on metadata that i has lots of columns. I think it will not wise decision to create 100+ columns in one table. If I do so I have to use MyISAM

If i make 4 or 5 tables (maybe 20+ columns for each table) then I need make one parent table and rest of tables uses foreign key to make connected each other to their parent. so in this case I need to us InnoDB engine.

When I run searching on my server, both cases will give me output properly but which one will take less time to give result?

Because first case just one table and I just fetch data with some condition but 2nd case I have to make join and search?

Please suggest me which one would be good design or if you have any good idea please share with me.

bdsarwar
  • 129
  • 1
  • 2
  • 9
  • This all depends on the semantic structure your data, how much of it there is, what types of "search" you need to perform... you need to provide considerably more information than you have. Better yet, you need to read up on [database design](http://en.wikipedia.org/wiki/Database_design). – eggyal May 01 '12 at 12:37
  • well as you know RETS has lots of fields like bedroom, bathroom, city etc. no FullText search will use here, but I want to know the best idea how to optimize this type of scenario as we have lots of fields and most of the searchable. – bdsarwar May 01 '12 at 12:59
  • FYI, you can use the Select option in a RETS Search to return selected fields instead of all fields. see http://retsdoc.onconfluence.com/display/rets172/7.4+Optional+Request+Arguments – yitwail Jul 26 '12 at 08:24

3 Answers3

2

Nearly all MLS RETS feeds have 3 sub-categories of information that can be divided into tables.

So you may have Residential, Rental, and Commercial tables.

Use MLS ID as primary key.

You can divide Residential table into

residentialPriceInfo

Fields

  • MLSID
  • ListPrice
  • UtilityFees
  • Community Fees
  • TaxID
  • Other fees

residentialAgentInfo

Fields

  • MLSID
  • ListAgentName
  • ListAgentPhone
  • ListOfficeName
  • ListOfficePhone

residentialDetailsInfo

Fields

  • MLSID
  • BathsFull
  • BathsHalf
  • TotalBeds
  • SquareFeet
  • Address
  • City
  • State
Andrew Briggs
  • 1,329
  • 12
  • 26
0

When you split the columns make sure to group them in term of access frequency/preference, in order to avoid multiple joins.

Eks Wi
  • 23
  • 3
0

The biggest (and simplest) performance improvement would be to split each board into its own separate table.

gavanon
  • 1,293
  • 14
  • 15