tec4 - 11:37 pm on Feb 14, 2012 (gmt 0)
@whoisgregg - thanks for the note on the primary key look-ups. I figured this previously but nothing really deemed it a super priority, in my mind, before because everything was running smoothly until the increase in traffic.
Since your post, I have gone ahead and switched my radius search to per-calculate and store the closest 20 items (by primary key) in a column in the table like: 'key1','key2',....,'key20'. Then upon page loading I use this to inject the keys into a function in one of my classes to search for the nearest items, like this:
select column1,column2,....,column12 from database.table where PrimaryKey in ($keys);
Planning on updating these entries once a week to make sure items are up to date and such. Does that seem like an appropriate way of going about it? Or is there a better way to store nearby entries in a table, etc?
@particleman - wish I could slap up a database server, I'd be on that in an instant! lol unfortunately would probably cost quite a bit to have my current hosting provider to do so - luckily the modification to the queries is helping quite a bit! Have modified them quite a bit so far - later today I will get on the running of queries with the explain keyword to try and hone them in even further - thanks for the recommendation :)
@Frank_Rizzo - will download those scripts tonight and see what it reports - thanks for pointing those out, had not heard of those so will check those out. Should definitely add a couple gigs of RAM in the box, as well, for sure.
Thank you all for the help!