tec4 - 9:30 pm on Feb 6, 2012 (gmt 0)
Ya, I have indexes on the database.
From what I can see, and from what I have been told from my hosting company's support department when they looked into it when having to re-boot and such, is that the mysql server is getting heavily loaded.
One problem I think i have is that quite a bit of my pages don't simply do a simple look-up query for a single item. A lot of my item specific pages (which are the ones getting the most traffic), and which all are dynamically created, search for the single item along with either similar items or items within a particular radius (which i think is a heavier recourse taking process).
I think there are probably a ton of more efficient ways to go about setting something like this up - but have not been able to com up with a better solution as of yet...
When I look at WHM on the server, under the Process Manager, mysql is always by far the highest process running. Other mysql tables on the server seem to run fast but they are also not being hit very hard and are a ton smaller in size.
Are there any tools/things you can do to try to find where things are bottle-necking?
On another note, the sites are not currently set up to cache the pages for X amount of days/hours...I am sure this would help a little bit but would really like to try and find out where my main problem is in programming is before moving to adding on that type of stuff.
An example set of queries that are probably my most commonly used are the below snippets from my detail pages (grab item and grab nearby items)...let me know if this is completely horrible to be running on the DB on every visit....I know I have quite a bit of improvement to make.
$query = "select * from `database`.`table`
where id='" . $db->escape($id) . "'";
$query2 = "SELECT column1, column2....column15,
( 3959 * acos( cos( radians('".$db->escape($lat)."') ) * cos( radians( Latitude ) ) * cos( radians( Longitude ) - radians('".$db->escape($lng)."') ) + sin(radians('".$db->escape($lat)."') ) * sin( radians( Latitude ) ) ) ) AS distance
WHERE City = '".$city."' AND status IN ('open','closed','pending') AND
((DATE_SUB(CURDATE(),INTERVAL 1 YEAR) <= DateClosed OR DATE_SUB(CURDATE(),INTERVAL 6 MONTH) <= EntryDate)) HAVING distance < ".$db->escape($radius)."
ORDER BY distance LIMIT 0 , 10";
Restating that I know it is probably horrible to have that run each page load (or at least I think it's probably horrible), just don't know how I could go about displaying such content on a page without running such resource intensive tasks.
Seeing the above, does it make more sense that mysql would be suffering a bit? Or, in your experience, should this still be manageable levels?
I really appreciate and thank you for your time.