?
Solved

Architectural of database with millions of searchable records

Posted on 2011-03-22
4
Medium Priority
?
239 Views
Last Modified: 2012-05-11
I am building a comparison site with around 500,000 product with maybe 30 data fields for each product.

I have little experience with something this scale (over a few thousand records), does anyone have any suggestions on how to ensure this is done efficient and how product pictures and files need to be organized for it being most efficient. I can code the logic but worried about efficiency and retrieving records quickly.

Would it be better to break the data into a bunch of tables? Does having a smaller table make it faster to search?

Does an auto suggest code work off directly from the db or some cached file produced elsewhere?

 
0
Comment
Question by:blink10
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 4

Expert Comment

by:qasim_md
ID: 35187629
Yes it is better to breakdown your table into 2 tables and maintain a Referential integrity to your tables.
0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 2000 total points
ID: 35188324
"Would it be better to break the data into a bunch of tables? Does having a smaller table make it faster to search?"

Yes. There is a process for this called NORMALISATION and if you follow its 3 main rules you will not go far wrong. There are examples of how to do this at Wikipedia http://en.wikipedia.org/wiki/Database_normalization#Example and you can also post database table definitions here at EE. The articles at Wikipedia can get a bit technical but read them and try and get the gist of what they are saying. In particular read these 3 and look at the examples.

http://en.wikipedia.org/wiki/First_normal_form
http://en.wikipedia.org/wiki/Second_normal_form
http://en.wikipedia.org/wiki/Third_normal_form

For 99% of database work, you can ignore all the other normal forms.


"Does an auto suggest code work off directly from the db or some cached file produced elsewhere?"

Auto suggest uses database data to work, but it usually requires some level of programming in PHP/MySQL and javascript.
0
 
LVL 5

Expert Comment

by:tsmgeek
ID: 35194790
or use latest mysql table partitioning if its too much hassle to split the table up
depends on the application really
dont go overboard as then its counter productive, say you have two pages onyour site, a summary and then a detailed page, think that thats how you could split your data
0
 
LVL 5

Expert Comment

by:innotionent
ID: 35198101
I always find that you need to look closely at your data structure, determine what data is related to each other and then group that data together in tables.

Then create a mapping table to tie all that data together.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question