Slow results

bengiles

Many sites I visit which have large amounts of data to sort through are very slow to load. Sites such as hot scripts, or telephone number searches, large forums, etc, are examples of this. Is this unavoidable with very large databases?

bubblenut

Well, yea. The more data you have to search through the longer it's going to take. There are many ways to make the searching quicker but they're all data specific.
Bubble

bengiles

Originally posted by bubblenut
Well, yea. The more data you have to search through the longer it's going to take. There are many ways to make the searching quicker but they're all data specific.
Bubble

Thanks Bubble, that answers an earlier post I made as well. Many small databases, at least for speed, would be preferable to one very large database.

bubblenut

No, that's not quite what I meant. If you've got the data accross different databases then it's going to be even more difficult (and take even longer) to search the whole lot. If you're storing huge amounts of data it will take time to search through it. There is no getting around that. You can have slave database servers which hold exactly the same data as the master database but on other machines to ease traffic on each database but distributing the actual data (by this I mean splitting it up and having a bit over here and a bit over there) is never really a good idea unless you absolutely need to. The arguments for needing to are also reducing now as machine speed & clustering technologies are advancing so quickly.
Is there a specific issue you are having difficulty with or worrying about? Are you going to be running a site with huge amounts of data? If so what kind of orders of data (100000 rows in the main table, 100000000 rows?)

bengiles

What I am developing is a combination business directory/classifieds/groups website. I have the initial data of over 10 million businesses for the business directory. The content available to visitors will be determined by their zip code. There are two ways I can go. One is to use one database and a zip/distance calculator which would allow the visitor to limit their results to 'xxx' number of miles surrounding their selected zip code. This would be by far the best choice for usability, but presents a nightmare relating to the size of the search initially plus having to filter all results from the origional query using the zip/distance calculations. My second option is to split all the data up into 3,200+ databases, one for each county in the US. This option would only return results from the county that their selected zip code was in. Not as nice as option as the first, but results would be very fast. I was still hoping their was a hardware/software arrangement that could let me use the first option with the speed of the second.

bubblenut

How often is the data going to be changed? If you can restrict chages to, say once a week. If customers have have the capability to make changes say that the master database only get's updated once a week and just store their changes untill then. Then when you come to update the master you can upload all the info, optimize the database and then replicate the data over to a number of slaves. You could also make lots of tables in the same database for each different county and also another table showing which of these counties border each other so you could build up a sort of map of the data you've got and it's relative geographical positioning. Queries might be a little tricky to write for it but once you've got them sorted it should work pretty well.