(Resolvido) Melhorar performance do MySQL

[Andréa Carvalho]

Estou com cerca de 170 mil registros em uma derminada tabela. Sem ordenação na query ela leva menos de um segundo para ser executada, mas com ordenação leva cerca de 20 segundos!

Apesar do ORDER BY estar deixando as queries muito lentas, preciso fazer ordenação....

Já criei índices, utilizei ANALYZE TABLE antes de executar as queries, tentei também utilizar uma view, configurar alguns parâmetros do my.ini conforme vi em alguns fóruns, tentei de tudo que li por aí, e apesar de ter melhorado um pouco (antes estava próximo dos 30 segundos) ainda está muito lento...

Obrigada pessoal...

Editado Agosto 13, 2008 por Andréa Carvalho

[Denis Courcy]

Estou com cerca de 170 mil registros em uma derminada tabela. Sem ordenação na query ela leva menos de um segundo para ser executada, mas com ordenação leva cerca de 20 segundos!

Apesar do ORDER BY estar deixando as queries muito lentas, preciso fazer ordenação....

Já criei índices, utilizei ANALYZE TABLE antes de executar as queries, tentei também utilizar uma view, configurar alguns parâmetros do my.ini conforme vi em alguns fóruns, tentei de tudo que li por aí, e apesar de ter melhorado um pouco (antes estava próximo dos 30 segundos) ainda está muito lento...

Obrigada pessoal...

Oi, Andréa!

Você poderia, por favor colocar a(s) estrutura(s) das tabela(s) envolvida(s) nesta query? Poderia, também colocar a própria query?

Gostaria se ver os índices e como você está otimizando a consulta, para que possa analisar e auxiliá-la.

Não precisa colocar os nomes verdadeiros das colunas, índices e tabelas. Bastam que estejam representando os tipos de dados e a formação original da(s) tabela(s) e da consulta.

[Andréa Carvalho]

Puxa Denis.... muitas tabelas bem grandes..... é de um sistema CRM... isso vai dar um post enorme....heheh

Tabelas:

vtiger_crmentity

vtiger_salesorder

vtiger_account

vtiger_quotes

vtiger_salesorder

vtiger_users

vtiger_groups

vtiger_contactdetails

vtiger_sobillads

vtiger_soshipads

vtiger_quotes

vtiger_sogrouprelation

Query:

SELECT vtiger_crmentity.crmid,vtiger_salesorder.subject,vtiger_account.accountname,vtiger_quotes.quoteid,vtiger_salesorder.total,CASE WHEN (vtiger_users.user_name IS NOT NULL) THEN vtiger_users.user_name ELSE vtiger_groups.groupname END AS user_name ,vtiger_contactdetails.contactid,vtiger_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 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_sogrouprelation

ON vtiger_salesorder.salesorderid = vtiger_sogrouprelation.salesorderid

LEFT JOIN vtiger_groups

ON vtiger_groups.groupname = vtiger_sogrouprelation.groupname

LEFT JOIN vtiger_users

ON vtiger_users.id = vtiger_crmentity.smownerid

WHERE vtiger_crmentity.deleted = 0 ORDER BY vtiger_salesorder.subject ASC LIMIT 0, 20

Estrutura das tabelas e índices:

-vtiger_crmentity

crmid int(19)

smcreatorid int(19)

smownerid int(19)

modifiedby int(19)

setype char(30)

description text

createdtime datetime

modifiedtime datetime

viewedtime datetime

status varchar(50)

version int(19)

presence smallint(1)

deleted smallint(1)

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 202363 crmid

smcreatorid_idx INDEX 6 smcreatorid

smownerid_idx INDEX 6 smownerid

modifiedby_idx INDEX 48 modifiedby

setype INDEX 8 setype 10

-vtiger_salesorder

salesorderid int(19)

subject varchar(100)

potentialid int(19)

customerno varchar(100)

quoteid int(19)

vendorterms varchar(100)

contactid int(19)

vendorid int(19)

duedate date

carrier varchar(200)

pending varchar(200)

type varchar(100)

adjustment decimal(25,3)

salescommission decimal(25,3)

exciseduty decimal(25,3)

total decimal(25,3)

subtotal decimal(25,3)

taxtype varchar(25)

discount_percent decimal(25,3)

discount_amount decimal(25,3)

s_h_amount decimal(25,3)

accountid int(19)

terms_conditions text

purchaseorder varchar(200)

sostatus varchar(200)

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 166545 salesorderid

vendorid_idx INDEX 4 vendorid

contactid_idx INDEX 40 contactid

subject INDEX 6 subject 5

-vtiger_account

accountid int(19)

accountname varchar(100)

parentid int(19)

account_type varchar(200)

industry varchar(200)

annualrevenue int(19)

rating varchar(200)

ownership varchar(50)

siccode varchar(50)

tickersymbol varchar(30)

phone varchar(30)

otherphone varchar(30)

email1 varchar(100)

email2 varchar(100)

website varchar(100)

fax varchar(30)

employees int(10)

emailoptout varchar(3)

notify_owner varchar(3)

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 27263 accountid

phone INDEX 9087 phone 10

accountname_idx INDEX 13631 accountname 20

account_type_idx INDEX 5 account_type 15

parentid INDEX 7 parentid

-vtiger_quotes

quoteid int(19)

subject varchar(100)

potentialid int(19)

quotestage varchar(200)

validtill date

contactid int(19)

currency varchar(100)

subtotal decimal(25,3)

carrier varchar(200)

shipping varchar(100)

inventorymanager int(19)

type varchar(100)

adjustment decimal(25,3)

total decimal(25,3)

taxtype varchar(25)

discount_percent decimal(25,3)

discount_amount decimal(25,3)

s_h_amount decimal(25,3)

accountid int(19)

terms_conditions text

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 11 quoteid

quotestage_idx INDEX 11 quotestage

potentialid_idx INDEX 11 potentialid

contactid_idx INDEX 11 contactid

-vtiger_salesorder

salesorderid int(19)

subject varchar(100)

potentialid int(19)

customerno varchar(100)

quoteid int(19)

vendorterms varchar(100)

contactid int(19)

vendorid int(19)

duedate date

carrier varchar(200)

pending varchar(200)

type varchar(100)

adjustment decimal(25,3)

salescommission decimal(25,3)

exciseduty decimal(25,3)

total decimal(25,3)

subtotal decimal(25,3)

taxtype varchar(25)

discount_percent decimal(25,3)

discount_amount decimal(25,3)

s_h_amount decimal(25,3)

accountid int(19)

terms_conditions text

purchaseorder varchar(200)

sostatus varchar(200)

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 166545 salesorderid

vendorid_idx INDEX 4 vendorid

contactid_idx INDEX 4 contactid

subject INDEX 6 subject 5

-vtiger_users

id int(11)

user_name varchar(255)

user_password varchar(30)

user_hash varchar(32)

cal_color varchar(25)

first_name varchar(30)

last_name varchar(30)

reports_to_id varchar(36)

is_admin varchar(3)

currency_id int(19)

description text

date_entered timestamp

date_modified timestamp

modified_user_id varchar(36)

title varchar(50)

department varchar(50)

phone_home varchar(50)

phone_mobile varchar(50)

phone_work varchar(50)

phone_other varchar(50)

phone_fax varchar(50)

email1 varchar(100)

email2 varchar(100)

yahoo_id varchar(100)

status varchar(25)

signature varchar(250)

address_street varchar(150)

address_city varchar(100)

address_state varchar(100)

address_country varchar(25)

address_postalcode varchar(9)

user_preferences text

tz varchar(30)

holidays varchar(60)

namedays varchar(60)

workdays varchar(30)

weekstart int(11)

date_format varchar(200)

hour_format varchar(30)

start_hour varchar(30)

end_hour varchar(30)

homeorder varchar(255)

activity_view varchar(200)

lead_view varchar(200)

imagename varchar(250)

deleted int(1)

defhomeview varchar(100)

confirm_password varchar(30)

tagcloud_view int(1)

internal_mailer varchar(3)

crypt_type varchar(20)

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 4 id

user_pass INDEX 4 user_password

user_name INDEX 4 user_name 15

first_name INDEX 4 first_name 20

last_name INDEX 4 last_name 20

user_hash INDEX 4 user_hash

is_admin INDEX 4 is_admin

title INDEX 2 title 20

-vtiger_groups

groupid int(19)

groupname varchar(100)

description text

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 3 groupid

groupname UNIQUE 3 groupname 5

-vtiger_contactdetails

contactid int(19)

accountid int(19)

salutation varchar(200)

firstname varchar(40)

lastname varchar(80)

email varchar(100)

phone varchar(50)

mobile varchar(50)

title varchar(50)

department varchar(30)

fax varchar(50)

reportsto varchar(30)

training varchar(50)

usertype varchar(50)

contacttype varchar(50)

otheremail varchar(100)

yahooid varchar(100)

donotcall varchar(3)

emailoptout varchar(3)

imagename varchar(150)

reference varchar(3)

notify_owner varchar(3)

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 8234 contactid

accountid_idx INDEX 8234 accountid

-vtiger_sobillads

sobilladdressid int(19)

bill_city varchar(30)

bill_code varchar(30)

bill_country varchar(30)

bill_state varchar(30)

bill_street varchar(250)

bill_pobox varchar(30)

-vtiger_soshipads

soshipaddressid int(19)

ship_city varchar(30)

ship_code varchar(30)

ship_country varchar(30)

ship_state varchar(30)

ship_street varchar(250)

ship_pobox varchar(30)

-vtiger_quotes

quoteid int(19)

subject varchar(100)

potentialid int(19)

quotestage varchar(200)

validtill date

contactid int(19)

currency varchar(100)

subtotal decimal(25,3)

carrier varchar(200)

shipping varchar(100)

inventorymanager int(19)

type varchar(100)

adjustment decimal(25,3)

total decimal(25,3)

taxtype varchar(25)

discount_percent decimal(25,3)

discount_amount decimal(25,3)

s_h_amount decimal(25,3)

accountid int(19)

terms_conditions text

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 11 quoteid

quotestage_idx INDEX 11 quotestage

potentialid_idx INDEX 11 potentialid

contactid_idx INDEX 11 contactid

-vtiger_sogrouprelation

salesorderid int(19)

groupname varchar(100)

Índices:

Nome chave Tipo Cardinalidade Campo

PRIMARY PRIMARY 1 salesorderid

groupname_idx INDEX 1 groupname 5

[Denis Courcy]

Oi, andrea,

O mysql está informando que você não enviou a estrutura da tabea vtiger_salesordercf

[Andréa Carvalho]

Desculpe......são tantas que acabei esquecendo dessa...

ela possui apenas o campo id q é chave primária:

-vtiger_salesordercf

salesorderid int(19)

[Denis Courcy]

Andréa,

Obrigado.

Pela análise inicial, verifiquei que está faltando índices para as tabelas e atributos abaixo relacionados.

vtiger_sobillads.sobilladdressid

vtiger_soshipads.soshipadsdressid,

vtiger_crementity.deleted.

Os dois primeiros podem ocasionar TABLE SCAN na consulta.

O último só vale a pena ser criado se o default for <> 0 ou se houver valores diferentes de 0 e 1 (pelo que entendi está representando true ou false). A existencia deste tipo de índice agilizará a pesquisa.

Outra opção para agilizar a consulta é usar a cláusula STRAIGHT_JOIN que força a leitura das tabelas na ordem em que você determinou no SELECT.

Teste e reporte se houve progresso.

[Andréa Carvalho]

Poxa!! STRAIGHT_JOIN foi fantástico!! consegui ganhar cerca de 4 segundos!!!

então....

vtiger_sobillads.sobilladdressid e vtiger_soshipads.soshipadsdressid já são chaves primárias, esqueci de mencionar.....

vtiger_crementity.deleted aceita sim, somente 0 e 1 e o default é 0, então acredito que não seja necessário criar índice para este campo, certo??

o STRAIGHT_JOIN foi muito bom, mas ainda preciso deixar essa query mais rápida......:(

Ela está levando agora cerca de 6 segundos, se eu retirar o order by (ORDER BY vtiger_salesorder.subject ASC) leva apenas 0.0038 segundos......

[Andréa Carvalho]

Denis, ganhei mais 3 segundos utilizando left() no order by:

ORDER BY left(vtiger_salesorder.subject,1) ASC

:D

to ficando mais animada agora....

você tem mais alguma sugestão?

[Denis Courcy]

Oi 'Andréa Carvalho'

Por favor me informe o retorno das duas consultas abaixo:

ANTES DE EXECUTAR, por favor aplique as modificações que você fez.

UTILIZE o utilitário de linha de comando do mysql (mysql.exe).

1o retorno COM order by

2o. retorno SEM order by.

NÃO o LIMIT

Objetivo avaliação de performance para a criação de uma view física temporária

EXPLAIN SELECT STRAIGHT_JOIN vtiger_crmentity.crmid, vtiger_salesorder .subject,
vtiger_account.accountname,
vtiger_quotes.quoteid,
vtiger_salesorder.total,
CASE WHEN (vtiger_users.user_name IS NOT NULL) 
THEN vtiger_users.user_name 
ELSE vtiger_groups.groupname END AS user_name,
vtiger_contactdetails.contactid,
vtiger_account.accountid 
FROM vtiger_salesorder FORCE INDEX (subject)
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 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_sogrouprelation ON vtiger_salesorder.salesorderid = vtiger_sogrouprelation.salesorderid
LEFT JOIN vtiger_groups ON vtiger_groups.groupname = vtiger_sogrouprelation.groupname
LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
WHERE vtiger_crmentity.deleted = 0 
ORDER BY vtiger_salesorder.subject ASC \G;

[Andréa Carvalho]

Oi, Denis.

Aqui está....com ORDER BY:

mysql> EXPLAIN SELECT STRAIGHT_JOIN vtiger_crmentity.crmid, vtiger_salesorder .subject,
    -> vtiger_account.accountname,
    -> vtiger_quotes.quoteid,
    -> vtiger_salesorder.total,
    -> CASE WHEN (vtiger_users.user_name IS NOT NULL)
    -> THEN vtiger_users.user_name
    -> ELSE vtiger_groups.groupname END AS user_name,
    -> vtiger_contactdetails.contactid,
    -> vtiger_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 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_sogrouprelation ON vtiger_salesorder.salesorderid = vtiger_sogrouprelation.salesorderid
    -> LEFT JOIN vtiger_groups ON vtiger_groups.groupname = vtiger_sogrouprelation.groupname
    -> LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
    -> WHERE vtiger_crmentity.deleted = 0
    -> ORDER BY left(vtiger_salesorder.subject,1) ASC \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_salesorder
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 166545
        Extra: Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_crmentity
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_salesorder.salesorderid
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_sobillads
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.crmid
         rows: 1
        Extra: Using where; Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_soshipads
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.crmid
         rows: 1
        Extra: Using where; Using index
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_salesordercf
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.crmid
         rows: 1
        Extra: Using index
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_quotes
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_salesorder.quoteid
         rows: 1
        Extra: Using index
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_account
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_salesorder.accountid
         rows: 1
        Extra: 
*************************** 8. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_contactdetails
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_salesorder.contactid
         rows: 1
        Extra: Using index
*************************** 9. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_sogrouprelation
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.crmid
         rows: 1
        Extra: 
*************************** 10. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_groups
         type: ref
possible_keys: groupname
          key: groupname
      key_len: 18
          ref: vtigercrm504.vtiger_sogrouprelation.groupname
         rows: 1
        Extra: 
*************************** 11. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_users
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.smownerid
         rows: 1
        Extra: 
11 rows in set (0.00 sec)

ERROR: 
No query specified

E sem ORDER BY:

mysql> EXPLAIN SELECT STRAIGHT_JOIN vtiger_crmentity.crmid, vtiger_salesorder .subject,
    -> vtiger_account.accountname,
    -> vtiger_quotes.quoteid,
    -> vtiger_salesorder.total,
    -> CASE WHEN (vtiger_users.user_name IS NOT NULL)
    -> THEN vtiger_users.user_name
    -> ELSE vtiger_groups.groupname END AS user_name,
    -> vtiger_contactdetails.contactid,
    -> vtiger_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 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_sogrouprelation ON vtiger_salesorder.salesorderid = vtiger_sogrouprelation.salesorderid
    -> LEFT JOIN vtiger_groups ON vtiger_groups.groupname = vtiger_sogrouprelation.groupname
    -> LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
    -> WHERE vtiger_crmentity.deleted = 0 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_salesorder
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 166545
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_crmentity
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_salesorder.salesorderid
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_sobillads
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.crmid
         rows: 1
        Extra: Using where; Using index
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_soshipads
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.crmid
         rows: 1
        Extra: Using where; Using index
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_salesordercf
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.crmid
         rows: 1
        Extra: Using index
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_quotes
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_salesorder.quoteid
         rows: 1
        Extra: Using index
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_account
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_salesorder.accountid
         rows: 1
        Extra: 
*************************** 8. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_contactdetails
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_salesorder.contactid
         rows: 1
        Extra: Using index
*************************** 9. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_sogrouprelation
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.crmid
         rows: 1
        Extra: 
*************************** 10. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_groups
         type: ref
possible_keys: groupname
          key: groupname
      key_len: 18
          ref: vtigercrm504.vtiger_sogrouprelation.groupname
         rows: 1
        Extra: 
*************************** 11. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_users
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: vtigercrm504.vtiger_crmentity.smownerid
         rows: 1
        Extra: 
11 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> exit;

[Denis Courcy]

Oi 'Andréa Carvalho'!

repita por favor usando o trecho de código FROM vtiger_salesorder FORCE INDEX (subject)

Não precisa me enviar Todos os resultados novamente.

Somente o resultado da tabela abaixo

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_salesorder
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 166545
        Extra: Using filesort

Informe, também, se houve ganho de tempo e quais os tempos para com e sem order by.

Objetivo: Avaliar a performance e a obediência do MySQL na forçação de barra com o uso do índice.

[Andréa Carvalho]

Com order by + FORCE INDEX:

mysql> EXPLAIN SELECT STRAIGHT_JOIN vtiger_crmentity.crmid, vtiger_salesorder .subject,
    -> vtiger_account.accountname,
    -> vtiger_quotes.quoteid,
    -> vtiger_salesorder.total,
    -> CASE WHEN (vtiger_users.user_name IS NOT NULL)
    -> THEN vtiger_users.user_name
    -> ELSE vtiger_groups.groupname END AS user_name,
    -> vtiger_contactdetails.contactid,
    -> vtiger_account.accountid
    -> FROM vtiger_salesorder FORCE INDEX (salesorder_subject) 
    -> 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 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_sogrouprelation ON vtiger_salesorder.salesorderid = vtiger_sogrouprelation.salesorderid
    -> LEFT JOIN vtiger_groups ON vtiger_groups.groupname = vtiger_sogrouprelation.groupname
    -> LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
    -> WHERE vtiger_crmentity.deleted = 0
    -> ORDER BY left(vtiger_salesorder.subject,1) ASC \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_salesorder
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 166545
        Extra: Using filesort

Sem order by, com FORCE INDEX:

mysql> EXPLAIN SELECT STRAIGHT_JOIN vtiger_crmentity.crmid, vtiger_salesorder .subject,
    -> vtiger_account.accountname,
    -> vtiger_quotes.quoteid,
    -> vtiger_salesorder.total,
    -> CASE WHEN (vtiger_users.user_name IS NOT NULL)
    -> THEN vtiger_users.user_name
    -> ELSE vtiger_groups.groupname END AS user_name,
    -> vtiger_contactdetails.contactid,
    -> vtiger_account.accountid
    -> FROM vtiger_salesorder FORCE INDEX (salesorder_subject) 
    -> 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 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_sogrouprelation ON vtiger_salesorder.salesorderid = vtiger_sogrouprelation.salesorderid
    -> LEFT JOIN vtiger_groups ON vtiger_groups.groupname = vtiger_sogrouprelation.groupname
    -> LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
    -> WHERE vtiger_crmentity.deleted = 0
    -> \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: vtiger_salesorder
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 166545
        Extra:

Sobre os tempos, em média, os valores abaixo:

Sem FORCE INDEX e com ORDER BY: 2.7 segundos

Sem FORCE INDEX e sem ORDER BY: 0.11 segundos

Com FORCE INDEX e com ORDER BY: 2.5 segundos

Com FORCE INDEX e sem ORDER BY: 0.07 segundos

[Denis Courcy]

Andréa

Concluindo a pesquisa. Apesar de tentar forçar o uso do índice, o ganho foi de apenas 4 decimos de segundo.

Sugiro a criação de uma tabela temporária que deverá ser criada, na RAM; carregada, a cada execução desta consulta; e destruida, apos a execuçao da query pára que a memoria seja liberada

Segue abaixo o modelo.

Creio que o ganho de resposta será valioso. De qualquer forma, já temos um ganho de 17.5 segundos, o que é muito bom.

create TEMPORARY table view_temp (
   crmid         int(19),
   subject       varchar(100),
   accountname   varchar(100),
   quoteid       int(19),
   total         decimal(25,3),
   user_name     varchar(255),
   contactid     int(19),
   accountid      int(19),
   INDEX subject (subject)
);ENGINE=MEMORY

INSERT INTO view_temp (crmid, subject, accountname, quoteid, total, user_name, contactid, accountid)
SELECT STRAIGHT_JOIN vtiger_crmentity.crmid, vtiger_salesorder.subject,
vtiger_account.accountname,
vtiger_quotes.quoteid,
vtiger_salesorder.total,
CASE WHEN (vtiger_users.user_name IS NOT NULL) 
THEN vtiger_users.user_name 
ELSE vtiger_groups.groupname END AS user_name,
vtiger_contactdetails.contactid,
vtiger_account.accountid 
FROM vtiger_salesorder FORCE INDEX (subject)
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 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_sogrouprelation ON vtiger_salesorder.salesorderid = vtiger_sogrouprelation.salesorderid
LEFT JOIN vtiger_groups ON vtiger_groups.groupname = vtiger_sogrouprelation.groupname
LEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid
WHERE vtiger_crmentity.deleted = 0

[Denis Courcy]

Andréa

Não esqueça de, se você adotar o recurso de tabela temporária, apontar seu código PHP para ler esta tabela.

A destruição da tabela temporária em memória se faz da mesma forma que a destruição de uma tabela comum.

DROP TABLE view_temp;

Andréa

Não esqueça de, se você adotar o recurso de tabela temporária, apontar seu código PHP para ler esta tabela.

A destruição da tabela temporária em memória se faz da mesma forma que a destruição de uma tabela comum.

DROP TABLE view_temp;

[Andréa Carvalho]

Oi Denis!!

Muito obrigada por tudo!!!

Eu vou fazer o seguinte....esse sistema que eu to trabalhando é bem grandinho.....e essa não é a única query problemática dele.... :/ então vou usar td que aprendi aqui nas queries mais críticas (já estou fazendo isso) e ver como vai ficar o sistema no geral....

Se ainda assim ficar muito lento, vou dar uma estudada melhor sobre essa view temporária que você me falou, porque não entendi muito bem como funciona......aí se eu tiver mais problemas ou duvidas trago pra cá denovo, ok???

Obrigadaaaa!!!