MySQL Forums
Forum List  »  Optimizer & Parser

Slow Query
Posted by: Deepak Nirmul
Date: June 21, 2019 04:47AM

I have very slow queries running Vtiger7.

I can see where the problem is but not sure how to execute.

Keep getting "ERROR 1327 (42000): Undeclared variable: N" when using the explain function.

I can see where the issue is but no idea how to execute the indexes or any other optimizations.

Also just what is N in this case.

The queries are below.

Count: 4 Time=1431.95s (5727s) Lock=0.00s (0s) Rows=15.8 (63),
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 INNER JOIN vtiger_crmentity ON vtiger_contactdetails.contactid = vtiger_crmentity.crmid INNER 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

Count: 6 Time=1145.04s (6870s) Lock=0.00s (0s) Rows=17.5 (105),
SELECT vtiger_contactdetails.firstname, vtiger_contactdetails.lastname, vtiger_contactdetails.title, vtiger_contactdetails.email, vtiger_crmentity.smownerid, vtiger_contactdetails.contactid, vtiger_crmentity_user_field.starred FROM vtiger_contactdetails INNER JOIN vtiger_crmentity ON vtiger_contactdetails.contactid = vtiger_crmentity.crmid LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid LEFT JOIN vtiger_crmentity_user_field ON vtiger_contactdetails.contactid = vtiger_crmentity_user_field.recordid AND vtiger_crmentity_user_field.userid=N WHERE vtiger_crmentity.deleted=N AND vtiger_contactdetails.contactid > N ORDER BY vtiger_crmentity.modifiedtime DESC LIMIT N,N

Count: 2 Time=1079.52s (2159s) Lock=0.00s (0s) Rows=17.5 (35),
SELECT vtiger_contactdetails.firstname, vtiger_contactdetails.lastname, vtiger_contactdetails.title, vtiger_contactdetails.email, vtiger_crmentity.smownerid, vtiger_contactdetails.contactid, vtiger_crmentity_user_field.starred FROM vtiger_contactdetails INNER JOIN vtiger_crmentity ON vtiger_contactdetails.contactid = vtiger_crmentity.crmid LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid LEFT JOIN vtiger_crmentity_user_field ON vtiger_contactdetails.contactid = vtiger_crmentity_user_field.recordid AND vtiger_crmentity_user_field.userid=N WHERE vtiger_crmentity.deleted=N AND vtiger_contactdetails.contactid > N ORDER BY vtiger_crmentity.modifiedtime DESC LIMIT N, N

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Query
482
June 21, 2019 04:47AM
142
June 21, 2019 10:17AM
146
June 24, 2019 02:18AM
152
June 24, 2019 09:56AM
148
June 24, 2019 10:15AM
165
June 24, 2019 10:45AM
108
September 04, 2019 11:47AM
118
September 04, 2019 02:32PM
115
September 06, 2019 05:59AM
133
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.