Need suggestion with MySQL queries !!!!!

[systemali]

Hello All,

I am a newbie with MySQL administration and I needs some assistance with few SELECT statements that i have with me as i think they are causing heavy processing loads on the server CPU.

Can any one please look into them and suggest changes in it ?

[CENTER] THERE ARE JUST 4 SELECT STATEMENTS IN THE ATTACHED FILE !!
[/CENTER]

I would appreciate any assistance in this regards.

Thank you all

[scott.nemes]

If you paste the queries in your post I’ll take a look, otherwise I’d suggest giving Percona’s online query analyzer tool a try:

[URL]MySQL Tools and Management Software to Perform System Tasks by Percona

[systemali]

Thank you so very very much for asking me to paste the queries over here.

Here we go :-

==========================

select vtiger_contactdetails.contact_no,vtiger_contactdetails.first name,vtiger_contactdetails.lastname,vtiger_contactdetails.fi rstname,vtiger_contactdetails.title,vtiger_contactdetails.ac countid,vtiger_contactdetails.email,vtiger_contactdetails.ph one,case when (vtiger_users.user_name not like ‘’) then vtiger_users.user_name else vtiger_groups.groupname end as user_name ,vtiger_crmentity.crmid,vtiger_account.accountid FROM vtiger_contactdetails INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_contactdetails.contactid INNER JOIN vtiger_contactaddress ON vtiger_contactaddress.contactaddressid = vtiger_contactdetails.contactid INNER JOIN vtiger_contactsubdetails ON vtiger_contactsubdetails.contactsubscriptionid = vtiger_contactdetails.contactid INNER JOIN vtiger_contactscf ON vtiger_contactscf.contactid = vtiger_contactdetails.contactid LEFT JOIN vtiger_account ON vtiger_account.accountid = vtiger_contactdetails.accountid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid LEFT JOIN vtiger_contactdetails vtiger_contactdetails2 ON vtiger_contactdetails.reportsto = vtiger_contactdetails2.contactid LEFT JOIN vtiger_customerdetails ON vtiger_customerdetails.customerid = vtiger_contactdetails.contactid WHERE vtiger_contactdetails.contactid > 0 AND vtiger_crmentity.deleted = 0 and (vtiger_contactdetails.phone LIKE ‘%9000000000%’ OR vtiger_contactdetails.mobile LIKE ‘%9000000000%’ OR vtiger_contactsubdetails.homephone LIKE ‘%9000000000%’ OR vtiger_contactsubdetails.otherphone LIKE ‘%9000000000%’ OR vtiger_contactdetails.email LIKE ‘%9000000000%’) LIMIT 0, 10

select vtiger_leaddetails.lead_no,vtiger_leaddetails.lastname,vtige r_leaddetails.firstname,vtiger_leaddetails.company,vtiger_le adaddress.phone,vtiger_leadsubdetails.website,vtiger_leaddet ails.email,case when (vtiger_users.user_name not like ‘’) then vtiger_users.user_name else vtiger_groups.groupname end as user_name ,vtiger_crmentity.crmid FROM vtiger_leaddetails INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_leaddetails.leadid INNER JOIN vtiger_leadsubdetails ON vtiger_leadsubdetails.leadsubscriptionid = vtiger_leaddetails.leadid INNER JOIN vtiger_leadaddress ON vtiger_leadaddress.leadaddressid = vtiger_leadsubdetails.leadsubscriptionid INNER JOIN vtiger_leadscf ON vtiger_leaddetails.leadid = vtiger_leadscf.leadid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid WHERE vtiger_leaddetails.leadid > 0 AND vtiger_crmentity.deleted = 0 AND vtiger_leaddetails.converted = 0 and (vtiger_leadaddress.phone LIKE ‘%9000000000%’ OR vtiger_leadaddress.mobile LIKE ‘%9000000000%’ OR vtiger_leaddetails.email LIKE ‘%9000000000%’) LIMIT 0, 10

select vtiger_salesorder.salesorder_no,vtiger_salesorder.subject,vt iger_salesorder.accountid,vtiger_salesorder.quoteid,vtiger_s alesorder.total,case when (vtiger_users.user_name not like ‘’) then vtiger_users.user_name else vtiger_groups.groupname end as user_name ,vtiger_crmentity.crmid,vtiger_contactdetails.contactid,vtig er_account.accountid FROM vtiger_salesorder INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_salesorder.salesorderid INNER JOIN vtiger_sobillads ON vtiger_salesorder.salesorderid = vtiger_sobillads.sobilladdressid INNER JOIN vtiger_soshipads ON vtiger_salesorder.salesorderid = vtiger_soshipads.soshipaddressid LEFT JOIN vtiger_salesordercf ON vtiger_salesordercf.salesorderid = vtiger_salesorder.salesorderid LEFT JOIN vtiger_currency_info ON vtiger_salesorder.currency_id = vtiger_currency_info.id LEFT OUTER JOIN vtiger_quotes ON vtiger_quotes.quoteid = vtiger_salesorder.quoteid LEFT OUTER JOIN vtiger_account ON vtiger_account.accountid = vtiger_salesorder.accountid LEFT JOIN vtiger_contactdetails ON vtiger_salesorder.contactid = vtiger_contactdetails.contactid LEFT JOIN vtiger_potential ON vtiger_potential.potentialid = vtiger_salesorder.potentialid LEFT JOIN vtiger_invoice_recurring_info ON vtiger_invoice_recurring_info.salesorderid = vtiger_salesorder.salesorderid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid WHERE vtiger_salesorder.salesorderid > 0 AND vtiger_crmentity.deleted = 0 and (vtiger_salesordercf.cf_733 LIKE ‘%9000000000%’ OR vtiger_salesordercf.cf_736 LIKE ‘%9000000000%’) LIMIT 0, 10

select vtiger_servicecontracts.contract_no,vtiger_servicecontracts. subject,vtiger_servicecontracts.sc_related_to,case when (vtiger_users.user_name not like ‘’) then vtiger_users.user_name else vtiger_groups.groupname end as user_name,vtiger_servicecontracts.start_date,vtiger_servicec ontracts.due_date,vtiger_servicecontracts.progress,vtiger_se rvicecontracts.contract_status ,vtiger_crmentity.crmid FROM vtiger_servicecontracts INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_servicecontracts.servicecontractsid INNER JOIN vtiger_servicecontractscf ON vtiger_servicecontractscf.servicecontractsid = vtiger_servicecontracts.servicecontractsid LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid LEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid LEFT JOIN vtiger_contactdetails ON vtiger_contactdetails.contactid =vtiger_servicecontracts.sc_related_to LEFT JOIN vtiger_account ON vtiger_account.accountid =vtiger_servicecontracts.sc_related_to WHERE vtiger_servicecontracts.servicecontractsid > 0 AND vtiger_crmentity.deleted = 0 and (vtiger_servicecontractscf.cf_718 LIKE ‘%9000000000%’ OR vtiger_servicecontractscf.cf_722 LIKE ‘%9000000000%’) LIMIT 0, 10

==============

Yes, I have used the percona analyzer and it did not find any fault in my queries

Please suggest.

Thank you,