Joining fields from multiple tables into one table

burnside

Hi,

I have a client who has a few tables that contain customer and billing information. When they perform a query on the website to see that information they notice a slower performance than what it was just a few months ago. I know that the more that these tables grow, the slower things will get.

I heard one way to solve this would be to combine the necessary fields from the multiple tables into one table. This way, the new table would contain the needed information and queries would run much quicker. Does anyone have any idea how this should be done? I'm thinking it would be a nightly job in which the day's newest records from the multiple tables would be added to the new single table.

Any help or pointers would be great!
b

cahva

No, that nightly thing is really not the right way. Heres couple of things you can do:

Check that indexing is done right on the tables(using foreign keys etc.). If theres no proper indexing, it will slow even with only couple of thousands of records if you do a join.
Check that queries are optimized.
Check database settings. Theres things like query cache, memory usage etc.
You can use sophisticated cache systems like Cache Lite or cache found on Zend Framework. Theres also external caches like memcached

Theres much more than you can do but those 1-3 are the first things to check before trying to do some ugly hack 🙂 Nr 4 is also important and will affect performance greatly. I myself use Cache Lite on heavy sites which is great and very easy to use.

burnside

Thanks cahva for the info! I know the tables I created will start getting very large. They should approach 500,000 rows sometime next year. I have some time right now to make sure I have a clean, speedy system, that's why I'm looking into creating a single read only table from the multiple tables. This is what cell phone and credit card companies do too. What do you think? Pros, Cons?

cahva

Sorry I have not had to denormalize yet but ofcourse it makes sense. Cons are that it will definately make organising your data harder and ofcourse there will be repetition of data. But drivespace is cheaper than horsepower 🙂

But 500,000 rows is peanuts really 😉 Proper databases should handle millions of records and still get speedy results if indexing and joins are done right.

Sorry I dont have all-ending and definite answer so keep on searching for the right answer.

If I were you, I would definately look into caching data as much as possible. Memcached would be very good to use as you can add as many memcached servers as you want when your application grows.