Slow Query
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