How to handle large database ?

Question

Hi,

I am working in one real-estate website and I have large database around 250 fields in table and 15 lakhs (1.5 million) records in table. I want to give searching functionality in website so, How should I design db such that I can search property fast from these 15 lakhs records. I want to make site like http://www.redfin.com/.

is it effective all 15 lakhs records should be in one table for searching ?

I don't understand how should I design the db? I am using PHP + Mysql and so I also want to ask that which storage engine(MyISAM,InnoDB etc.) preferred for this type of large database handling ?

Please Help me out.

Answer 1

Are you saying that your database consists of one table? That's what 250 fields in a single table suggests to me. If so, I'd recommend consulting with a design expert to have your schema normalized a bit.

As far as performance goes, MySQL InnoDB should be sufficient as long as you design proper keys and indexes. The trick will be to know about the queries that you'll need and creating indexes to make them as efficient as possible.

Your table might have 250 columns, but I'm betting that typical queries will go after combinations of columns most of the time. Those combinations are what you'll need to index.

15 million rows isn't a staggering number. You might find that normalizing will reduce that number a great deal, because you won't have as much repeated data. Really large databases measure in terabytes.

Answer 2

I think you need to consider normalisation (Google it, read up and then come back and open some new questions if need be).

The number of records has to be very large to affect performance, where very large depends on the RDBMS being used. E.g. 15 million is huge for Access, mid-range for Oracle. But the data structure will matter in any RDBMS. So normalisation is the key.