Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Unanswered: application architecture

    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.
    Last edited by ruFog; 07-22-09 at 11:26.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    I`v added.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Oh I knew what it meant - I just couldn't picture why the 560 was there at the front.

  7. #7
    Join Date
    Jul 2009
    Posts
    3
    mike_bike_kite
    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.

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •