View Full Version : Puzzler -- SLOW Query Troubles

03-02-2007, 02:49 AM
Hey All,

I have carbon coppies of a crm_db running on 3 different servers
1 Desktop Development/Admin server, and
2 Tablet PCs in the field.
All are running same WAMP5 Stack on XP.

Apache version :Apache/2.0.55 (Win32)
PHP version :5.1.2 Loaded extensions :
bcmath, calendar, com_dotnet, ctype, date, ftp, iconv, odbc, pcre, Reflection, session, libxml, standard, tokenizer, zlib, SimpleXML, dom, SPL, wddx, xml, xmlreader, xmlwriter, apache2handler, mbstring, curl, gd, imap, mysql, mysqli, pdf

MySQL version :5.0.18-nt

So here's the mystery... We recently began getting max execution time errors (30 seconds) upon logging into our SugarCRM web app, but ONLY on the two tablets, NEVER on the Desktop, and the onset of the problem arose weeks apart for each of the two tablets.

Enabling slow_query logging, I have narrowed it down to two problem queries which -- if run in phpMyAdmin's query browser -- consistently execute in under 2 seconds on the Desktop, and consistently take over 16 seconds when run via PMA on the laptops.

I was thinking the problem must be the result of a malformed query, but the suspect queries run just FINE, on the Desktop/Development platform, while they are unacceptably slow on the tablet/production platforms which are -- as I have already stated -- effectively carbon copies of the Development stack.

I'm completely stumped. How might I track this thing down?

Here's the primary suspect query for reference:

SELECT emails.*, CONCAT(emails.date_start, CONCAT(' ', emails.time_start)) date_sent, users.user_name as assigned_user_name, contacts.first_name, contacts.last_name
FROM emails LEFT JOIN emails_contacts ec
ON emails.id = ec.email_id
LEFT JOIN contacts
ON ec.contact_id = contacts.id
ON emails.assigned_user_id=users.id
WHERE emails.assigned_user_id
IN ('226ce263-530c-b845-624c-451319c1dbe8', '3282e710-d3cf-1cdc-b928-451c37a8c850', '363b2d16-75a2-27d9-51d9-4518301a4e51', '5a35c345-cfd0-76f5-5f02-451c34100e41', 'd965b1d1-1e0f-99b0-2b7d-451c384904d7', 'ebf0ffde-2063-3e3c-bc77-451c355a6cc4', 'ef3c525f-3f53-e151-47dd-451af0d18f6c')
AND emails.type = 'inbound' AND emails.deleted=0 ORDER BY date_sent, date_entered asc
LIMIT 0,100;