Solved

Architectural of database with millions of searchable records

Posted on 2011-03-22
4
237 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 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

627 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