rocknbil - 3:41 pm on Sep 14, 2010 (gmt 0)
Correct, numeric lookups will **always** be faster, but when dealing with large record sets you need to normalize your database tables.
The problem, or at least a large part of it, is your tables aren't normalized. Look at this section:
Wow. :-) First, does every record have all these values complete or are a lot of them empty? Will many of these records have the exact same value in the same fields? What I'm getting at is this table can be made narrower by parting this off as two other tables and joining on them only when needed.
Field Type Null Key Default Extra
ListingID bigint(15) NO PRI NULL auto_increment
State_Abbrev varchar(5) NO NULL
(Region fields removed ...)
id int(11) |ListingID bigint(15) | regionID int(11)
So now you have a long list of numeric values to query. Numeric queries will always be lightspeed faster than text queries.
Next you have the actual regions table.
id int(11) | regionName varchar(50) | region_slug varchar(50)
(or maybe RegionName and region_slug are the same thing, only one needed, better yet.)
A typical select might be
select Listings.*,Regions.RegionName,Regions.region_slug from Listings,listingRegions,Regions where Listings.ListingID=listingRegions.ListingID and listingRegions.RegionID=Regions.id
- You will only have as many region records in listingRegions as you need for each listing, no overhead.
- The number of regions for each listing are unlimited, but can be as few as one. You are no longer limited to 14 regions.
- Your Regions table can grow as you need it, it can have five regions or five million.
- When you build your select lists in forms for regions, you no longer have to edit select lists, you just pull the values dynamically directly from the Regions table.
- Caveat: Currently when someone is adding a listing, do they enter a textual value or select from a list? If they are entering text values, this plays hell on your searches - UperWest Side, Upper West Side, and upper westside will all be different values and will never match in a user-entered search. The previous approach will eliminate that, and, it will be faster. Instead of
.... where region='Upper West Side'
.... where region='1234'
Again eliminating a textual query. You don't even need to use the join in a user search - just query the value, then query the region table when they click for details.
Anywhere you can eliminate a text value for a numeric value, you should. Another example:
State_Abbrev varchar(5) NO NULL
If these are U.S. states, you only need two characters - another optimization, make your fields only as wide as you need them. Better yet, build a states table
id int(11) |abbrev char(2) |full_name varchar(20)
then in your listings table,
State int(3) NO NULL
And you have another join - PLUS - you have the choice of selection the abbreviated or full name depending on the context. Same is true of countries, if you use them. This makes your queries more complex, but as your database grows you will be glad you did.
Try a search in Google for normalization site:webmasterworld.com for many more helpful threads around here.