application architecture

ruFog · #1 (**permalink**) 07-22-09, 09:51

Hello!

I`v made website with the database of some goods. At the beginning site was assigned for local market. Descriptions of the goods, user accounts are stored in the one DB, in which amount of rows approaches ~15000. One of my competitive advantage is flexible filters. But I have technical troubles - complex SQL queries, while filtering by several criterias. Now I want to add new regions. What would be better: one solid DB or several DB for each region? In the first case I`m afraid that my server will be high loaded. In the second case user accounts must be common to all DBs and I don`t know how to realize it.

This is my table structure(YAML format):

Code:

  ndvbase:
    _attributes: { phpName: ndvBase }
    id: { type: integer, required: true, primaryKey: true, autoIncrement: true }
    type: { type: integer, required: true, foreignTable: ndvbase_type, foreignReference: id, index: true }
    region: { type: integer, required: true, foreignTable: city, foreignReference: id }
    position_location: { type: integer, required: true, foreignTable: position_location, foreignReference: id, index: true }
    position_district: { type: integer, foreignTable: position_district, foreignReference: id, index: true }
    position_street: { type: integer, foreignTable: position_street, foreignReference: id, index: true }
    position_street_str: { type: char, size: 100 }
    position_homenumber: { type: char, size: 4 }
    suburb: { type: boolean, required: true, default: false }
    num_of_room: { type: tinyint, index: true }
    num_of_sellroom: { type: tinyint, index: true }
    num_of_bath: { type: tinyint }
    num_of_garage: { type: tinyint }
    sellroom_area: { type: float, size: 4, scale: 1 }
    floor: { type: tinyint, index: true }
    upper_floor: { type: tinyint }
    building_type: { type: tinyint, foreignTable: ndvbase_buildingtype, foreignReference: id, index: true }
    planning: { type: tinyint, foreignTable: ndvbase_planning, foreignReference: id, index: true }
    room_type: { type: varchar, size: 100 }
    lavatory: { type: varchar, size: 100 }
    balcony: { type: varchar, size: 100 }
    plumbing: { type: boolean, default: false }
    elec: { type: boolean, default: false }
    gas: { type: varchar, size: 100 }
    firing: { type: varchar, size: 100 }
    firingquick: { type: boolean, default: false }
    canalization: { type: boolean, default: false }
    total_area: { type: float, size: 4, scale: 1 }
    living_area: { type: float, size: 4, scale: 1 }
    kitchen_area: { type: float, size: 4, scale: 1 }
    earth_area: { type: varchar, size: 255 }
    land_designation: { type: tinyint, foreignTable: ndvbase_land_designation, foreignReference: id, index: true }
    office_class: { type: tinyint, foreignTable: ndvbase_office_class, foreignReference: id, index: true }
    ceilheight: { type: float, size: 4, scale: 1 }
    hasphone: { type: boolean, index: true }
    phonelines: { type: smallint }
    hasinternet: { type: boolean, default: false }
    state: { type: tinyint, foreignTable: ndvbase_state, foreignReference: id, index: true }
    furniture: { type: boolean, default: false }
    tech: { type: boolean, default: false }
    state_building: { type: tinyint, foreignTable: ndvbase_state_building, foreignReference: id, index: true }
    comment: { type: longvarchar, required: false }
    security: { type: boolean, default: false }
    buildedat: { type: date, required: false }
    carplace: { type: smallint }
    parking: { type: boolean, default: false }
    bycredit: { type: boolean, default: false }
    price: { type: integer, index: true }
    pricecontract: { type: boolean, default: false }
    contact_phone: { type: char, size: 50 }
    warning_by_phone: { type: boolean, default: false }
    published_by_user: { type: integer, required: false, foreignTable: sf_guard_user, foreignReference: id }
    photocount: { type: tinyint, required: true, default: 0 }
    onlyforusers: { type: tinyint, required: true, default: 0 }
    publishedtil: { type: date, required: false }
    vip: { type: boolean, default: false }
    position_location_tmp: { type: longvarchar, required: false }
    position_location_quick_tmp: { type: char, size: 80, required: false }
    position_district_tmp: { type: char, size: 80, required: false }
    building_type_tmp: { type: char, size: 80, required: false }
    planning_tmp: { type: char, size: 100, required: false }
    room_type_tmp: { type: char, size: 80, required: false }
    lavatory_tmp: { type: char, size: 80, required: false }
    balcony_tmp: { type: char, size: 80, required: false }
    firing_tmp: { type: char, size: 80, required: false }
    total_area_tmp: { type: char, size: 200, required: false }
    land_designation_tmp: { type: char, size: 150, required: false }
    office_class_tmp: { type: char, size: 50, required: false }
    state_tmp: { type: char, size: 100, required: false }
    state_building_tmp: { type: char, size: 100, required: false }
    price_tmp: { type: char, size: 20, required: false }
    contact_phone_tmp: { type: char, size: 50, required: false }
    rank: { type: tinyint, required: true, default: 0 }
    created_at: { type: date, required: true }
    updated_at: ~

This is example of general SQL query:

Code:

SELECT * FROM `ndvbase` WHERE ndvbase.CITY=:p1 AND ndvbase.TYPE=:p2 AND ndvbase.ONLYFORUSERS=:p3 AND ndvbase.POSITION_DISTRICT IN (:p4) AND ndvbase.NUM_OF_ROOM IN (:p5) AND ndvbase.STATE IN (:p6,:p7) AND ndvbase.PRICE>=:p8 ORDER BY ndvbase.CREATED_AT DESC,ndvbase.RANK DESC LIMIT 560, 20

Need advice. Help me please. Thanks.

r937 · #2 (**permalink**) 07-22-09, 10:14

Quote:

Originally Posted by ruFog

This is my table structure(YAML format):

YAML?

i don't see a region column, have you thought about adding one?

ruFog · #3 (**permalink**) 07-22-09, 10:26

I`v added.

mike_bike_kite · #4 (**permalink**) 07-22-09, 10:41

Quote:

Originally Posted by r937

YAML

I guess it stands for Yet Another Mystifying Layout.

Quote:

Originally Posted by ruFog

But I have technical troubles - complex SQL queries, while filtering by several criterias.

I assume there are lookup values for these fields and that you build the SQL on the fly. Why does it matter about the complexity of the SQL? If the user has a complex requirement then it's going to be a complex bit of SQL but it's the program that writes the code. Do you ensure that a user must set certain fields ie city and are these fields indexed? Can't you then just add a region field to your table?

Didn't understand the limit 560,20 part of the SQL.

Not sure how the ORDER BY ndvbase.CREATED_AT DESC,ndvbase.RANK works either. Wouldn't it be better to order by price or by user feedback on the property / landlord.

Quote:

Originally Posted by ruFog

What would be better: one solid DB or several DB for each region?

One solid DB would be better - the SQL should be easily fast enough to handle the data assuming the table is indexed and you're using the index.

r937 · #5 (**permalink**) 07-22-09, 10:51

Quote:

Originally Posted by mike_bike_kite

Didn't understand the limit 560,20 part of the SQL.

this is very common in mysql, and is used on web pages which show only 20 search results at a time

LIMIT 560,20 means skip 560 rows and show the next 20

although why a user would still be paging through results after having hit "Show next 20 results" 28 times is perhaps indicative of something else...

mike_bike_kite · #6 (**permalink**) 07-22-09, 10:57

Oh I knew what it meant - I just couldn't picture why the 560 was there at the front.

ruFog · #7 (**permalink**) 07-22-09, 11:14

mike_bike_kite

Quote:

One solid DB would be better - the SQL should be easily fast enough to handle the data assuming the table is indexed and you're using the index.

I generated about 25000 rows and standard query like I posted above was executed for more than 2 sec.

mike_bike_kite · #8 (**permalink**) 07-22-09, 11:18

ruFog,

Another small thing might be to use a hierarchy table to hold the location rather than have a separate field for state, city, region etc ( I'm guessing that region is a bit like area with a city ). This would allow a user to drill down to the area they're interested in. It would also be easy then to expand the system to other countries if that was desired.

If a user enters his criteria and he's getting back 560 results then it sounds like you need a better way of letting the user find the right result. Perhaps totalling results by the next level of location or grouping the results into price bands or number of rooms etc. It would take a pretty devoted user to wander through 560 results.

Do we get to play with the web site to - it makes all this far more fun.

mike_bike_kite · #9 (**permalink**) 07-22-09, 11:22

Quote:

Originally Posted by ruFog

I generated about 25000 rows and standard query like I posted above was executed for more than 2 sec.

What fields are indexed?
Are you always using them?
Why return 25000 records from a SQL query? surely you should force the user to be more specific in their request or just give them the first 100 results but also give them the option to refine their query perhaps by location or price etc (see previous post).

Internet Services

Web Development

Digital Marketing

Consumer Tech