http://qs321.pair.com?node_id=514834


in reply to OT: Scalable web application architecture

Maybe you need to think about the problem a bit differently. My last big a-ha! moment was when I realised I'd designed my database for data storage when what I should have been doing is designing it for data retrieval..

It sounds like you want to be able to do a query something like:

select room from search_table where max_capacity >= ? and pack_guests >= ? and pack_guests <= ? and valued_guest=? and agent=? and referral=?

The idea is to turn things around so instead of trying to apply a set of rules to a table of data you put the rules in a table and use it to look up the data.

In my case I was trying to process orders taking into account available stock, order payment, dispatch option selected by customer etc, etc. So to do a search for orders ready to be processed there were lots of lookups and calculating here there and everywhere to collect all the data needed for each order. I re-organised the order data so each order record had everything I needed and allowed me to do a single (although not simple) query to get all the orders ready to be processed. Once I had the data laid out correctly the database could easily and quickly process the query.

On the database config side of things.. have you modified your mysql config to allow it to use as much memory as you think it has available? Tuning Server Parameters

cheers,

J

  • Comment on Re: OT: Scalable web application architecture

Replies are listed 'Best First'.
Re^2: OT: Scalable web application architecture
by badaiaqrandista (Pilgrim) on Dec 07, 2005 at 15:10 UTC

    Yes. That is exactly what I was planning to do with this table. Instead of looping over all combination of rooms and packages, I put them all in one table and create a query like you said. The problem is in keeping it up to date with the actual data, which reside in their own table in every property database (e.g.: room table, package table, season table, etc...). However, can you give me an example of how a data structure for data retrieval differ from data storage?

    I'll look into MySQL tuning again. It seems that everyone points me to it, so there must be something there.

    badaiaqrandista
[reply]

      storage vs. retrieval typically comes down to optimizing for space vs. optimizing for time.

      For instance, there's the concept of data normalization -- you associate data with its main identifier (eg, a person has a address, and a reservation has a person, and a reservation has a room, and a room has a capacity, and on down the chain). The problem comes when you need to retrieve data -- it's stored in a very compact manner, but the system has to join multiple tables together to do its basic day-to-day operations. (and what if something changes? When Bob Smith moves, do we want it to reflect that his bill from the stay last year was sent to his old address, as opposed to his new address?)

      Often, when you're optimizing for retrieval, you have more indexes (based on how you're going to look for the data -- when optimizing for storage, you just need them for maintaining unique constraints (PK is a type of unique contraint), so you won't do things like having multi-field indexes (so you can pull out the most commonly used fields from the table, without reading in the whole record), and other such tricks.

      Um... I could ramble on for hours on this, but that should give you a quick idea of the issues.

      Oh -- and have you even tried analyzing the app? eg, check to where the program is spending all of its time?

[reply]
Re^2: OT: Scalable web application architecture
by crouchingpenguin (Priest) on Dec 08, 2005 at 00:38 UTC
    This is the exact use of views in databases, that allow you to do complex joins in a single select call between your app and the db. This lets the db optimize the query and lets you keep your tables normalized.

    cp
    ----
    "Never be afraid to try something new. Remember, amateurs built the ark. Professionals built the Titanic."
[reply]

In Section Seekers of Perl Wisdom