application architecture

[ruFog]

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):
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 }
city: { 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:
SELECT * FROM `ndvbase` WHERE ndvbase.CITY=1 AND ndvbase.TYPE=2 AND ndvbase.ONLYFORUSERS=3 AND ndvbase.POSITION_DISTRICT IN p4) AND ndvbase.NUM_OF_ROOM IN p5) AND ndvbase.STATE IN p6,7) AND ndvbase.PRICE>=8 ORDER BY ndvbase.CREATED_AT DESC,ndvbase.RANK DESC LIMIT 560, 20

Need advice. Help me please. Thanks.

 

[r937]

add a region column and use a single table, not multiple tables

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon