MySQL Forums
Forum List  »  Optimizer & Parser

Re: Slow Query
Posted by: Peter Brawley
Date: June 21, 2019 10:17AM

If you want help with this query, why not post it in human-readable form?

eg ...
SELECT 
  vtiger_contactscf.cf_632, vtiger_contactdetails.firstname, vtiger_contactdetails.lastname, 
  vtiger_contactscf.cf_1425, vtiger_contactdetails.email, vtiger_contactscf.cf_630, 
  vtiger_contactscf.cf_634, vtiger_contactdetails.mobile, vtiger_contactscf.cf_744, 
  vtiger_contactdetails.contactid, vtiger_crmentity_user_field.starred 
FROM vtiger_contactdetails 
JOIN vtiger_crmentity                 ON vtiger_contactdetails.contactid = vtiger_crmentity.crmid 
JOIN vtiger_contactscf                ON vtiger_contactdetails.contactid = 
                                         vtiger_contactscf.contactid 
LEFT JOIN vtiger_crmentity_user_field ON vtiger_contactdetails.contactid = 
                                         vtiger_crmentity_user_field.recordid 
                                     AND vtiger_crmentity_user_field.userid=N 
LEFT JOIN vtiger_users                ON vtiger_crmentity.smownerid = vtiger_users.id 
LEFT JOIN vtiger_groups               ON vtiger_crmentity.smownerid = vtiger_groups.groupid 
WHERE vtiger_crmentity.deleted=N 
  AND ( (( (trim(CONCAT(vtiger_users.first_name,'S',vtiger_users.last_name)) = 'S' 
        or vtiger_groups.groupname = 'S')) )
      ) 
  AND vtiger_contactdetails.contactid > N ORDER BY vtiger_crmentity.modifiedtime DESC LIMIT N,N ;
\
"N" is an undefined value. The query can't run till N is replaced by a value or by a reference supplied in the query.

So you'll need to fix that, then run Explain on it. If you need help turning that result into optimisations, please post the Explain result here inside BBCode code tags, and also post the Show Create Table results for each table, plus the value of innodb_buffer_pool_size.

Options: ReplyQuote


Subject
Views
Written By
Posted
456
June 21, 2019 04:47AM
Re: Slow Query
129
June 21, 2019 10:17AM
138
June 24, 2019 02:18AM
144
June 24, 2019 09:56AM
142
June 24, 2019 10:15AM
144
June 24, 2019 10:45AM
99
September 04, 2019 11:47AM
107
September 04, 2019 02:32PM
109
September 06, 2019 05:59AM
124
September 06, 2019 09:57AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.