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
1168
June 21, 2019 04:47AM
571
June 21, 2019 10:17AM
544
June 24, 2019 02:18AM
456
June 24, 2019 09:56AM
465
June 24, 2019 10:15AM
525
June 24, 2019 10:45AM
487
September 04, 2019 11:47AM
473
September 04, 2019 02:32PM
436
September 06, 2019 05:59AM
505
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.