List:Cluster« Previous MessageNext Message »
From:Johan Andersson Date:November 25 2010 10:40am
Subject:Re: reason for slow query ?
View as plain text  
Hi,

Perhaps PRIMARY is not the best index.

Try to use 'tan_list' instead.

SELECT  *  FROM tan_list FORCE INDEX(tan_list) WHERE (SEC_TYPE='Card' AND
TAN_LIST_NUMBER='1' AND USER_ID='1023029' AND CLIENT_ID='1' );

BR
johan

23 nov 2010 kl. 14.16 skrev Hendrik Woltersdorf:

> 
> Hi,
> 
> I have a MySQL cluster version 7.0.13 running on 4 machines (2x mysqld as
> API node, 2x data node with ndbmtd). One API node logged a slow query:
> 
> # Time: 101123 13:33:40
> # User@Host: pitase[pitase] @  [62.192.204.14]
> # Query_time: 3.446779  Lock_time: 0.000057 Rows_sent: 0  Rows_examined: 0
> SET timestamp=1290515620;
> SELECT  *  FROM tan_list WHERE (SEC_TYPE='Card' AND TAN_LIST_NUMBER='1' AND
> USER_ID='1023029' AND CLIENT_ID='1' );
> 
> When I execute this query manually it is _very_ fast and "explain extended"
> tells me, that it uses the primary key of the table:
> 
> CREATE TABLE `tan_list` (
>  `client_id` varchar(10) NOT NULL,
>  `sec_type` varchar(10) NOT NULL,
>  `user_id` varchar(30) NOT NULL,
>  `state` int(11) NOT NULL,
>  `tan_list_number` varchar(8) NOT NULL,
>  `tan_data` blob,
>  `tan_count` int(11) DEFAULT NULL,
>  `open_tan_count` int(11) DEFAULT NULL,
>  `error_count` int(11) DEFAULT NULL,
>  `letter_request_time` datetime DEFAULT NULL,
>  `activation_time` datetime DEFAULT NULL,
>  `letter_recipient` varchar(1024) DEFAULT NULL,
>  `letter_request_type` varchar(50) DEFAULT NULL,
>  `time_insert` datetime DEFAULT NULL,
>  `letter_sent_time` datetime DEFAULT NULL,
>  `card_number` varchar(16) DEFAULT NULL,
>  `ref_id` varchar(36) DEFAULT NULL,
>  `ref_id_next` varchar(36) DEFAULT NULL,
>  PRIMARY KEY (`client_id`,`sec_type`,`user_id`,`tan_list_number`),
>  KEY `tan_list` (`user_id`,`client_id`,`sec_type`,`tan_list_number`),
>  KEY `tan_list_idx1` (`card_number`),
>  KEY `tan_list_idx2` (`state`),
>  KEY `tan_list_idx3` (`open_tan_count`)
> ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
> 
> This table contains > 194000 rows. There were no other slow queries logged
> at this time on the two API nodes.
> 
> The API nodes use ndb-cluster-connection-pool=4.
> 
> Does anyone have an idea, what causes this long query time ?
> 
> kind regards
> 
> Hendrik Woltersdorf
> XCOM AG
> Email: Hendrik.Woltersdorf@stripped
> 
> 
> *** XCOM AG Legal Disclaimer ***
> 
> Diese E-Mail einschliesslich ihrer Anhaenge ist vertraulich und ist allein
> für den Gebrauch durch den vorgesehenen Empfaenger bestimmt. Dritten ist
> das Lesen, Verteilen oder Weiterleiten dieser E-Mail untersagt. Wir bitten,
> eine fehlgeleitete E-Mail unverzueglich vollstaendig zu loeschen und uns
> eine Nachricht zukommen zu lassen.
> 
> This email may contain material that is confidential and for the sole use
> of the intended recipient. Any review, distribution by others or forwarding
> without express permission is strictly prohibited. If you are not the
> intended recipient, please contact the sender and delete all copies.
> 
> Hauptsitz: Bahnstrasse 37, D-47877 Willich, USt-IdNr.: DE 812 885 664
> Kommunikation: Telefon +49 2154 9209-70, Telefax +49 2154 9209-900,
> www.xcom.de
> Handelsregister: Amtsgericht Krefeld, HRB 10340
> Vorstand: Matthias Albrecht, Dirk Franzmeyer, Dr. Rainer Fuchs, Marco
> Marty, Dirk Werner
> Vorsitzender des Aufsichtsrates: Stefan H. Tarach

Thread
reason for slow query ?Hendrik Woltersdorf23 Nov
  • Re: reason for slow query ?Johan Andersson25 Nov
    • Antwort: Re: reason for slow query ?Hendrik Woltersdorf25 Nov
      • RE: Antwort: Re: reason for slow query ?Andrew Armstrong25 Nov
        • Antwort: RE: Antwort: Re: reason for slow query ?Hendrik Woltersdorf26 Nov