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
550
June 21, 2019 04:47AM
Re: Slow Query
186
June 21, 2019 10:17AM
183
June 24, 2019 02:18AM
185
June 24, 2019 09:56AM
185
June 24, 2019 10:15AM
206
June 24, 2019 10:45AM
143
September 04, 2019 11:47AM
155
September 04, 2019 02:32PM
148
September 06, 2019 05:59AM
170
September 06, 2019 09:57AM


Sorry, you can't reply to this topic. It has been closed.

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.