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
456
June 21, 2019 04:47AM
130
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.