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
