CODEWITHSUNDEEP
phpmysqlrets
bdsarwar
bdsarwar

Reputation: 129

RETS and how to design our database table as it has lots of column and Query performance issues

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.

Upvotes: 2

Views: 1075

Answers (3)

gavanon
gavanon

Reputation: 1367

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

Upvotes: 0

Eks Wi
Eks Wi

Reputation: 23

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

Upvotes: 0

Andrew Briggs
Andrew Briggs

Reputation: 1349

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

Upvotes: 2

Related Questions