[Løst] Treg enkel Update (MySQL)

[drahcir]

Har problemer med en Update som går veldig sakte. Den er på formen:

UPDATE Name SET FirstName='',LastName='' WHERE NameId IN (SELECT A_NameId FROM 
AA,TempApplication WHERE AA.Applicant_ApplicantId=TempApplicationId);

Jeg har flere nøyaktig like med byttet ut A_NameId med f.eks. C_NameId. Noen går raskt.

AA er InnoDB og SHOW INDEX gir:

+-------+------------+--------------+--------------+-------------------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name                         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+--------------+--------------+-------------------------------------+-----------+-------------+----------+--------+------+------------+---------+
| AA    | 0          | PRIMARY      | 1            | Application_ApplicationId           | A         | 190093      | NULL     |        |      | BTREE      |         |
| AA    | 0          | PRIMARY      | 2            | Applicant_ApplicantId               | A         | 190093      | NULL     |        |      | BTREE      |         |
| AA    | 1          | AA_FKIndex1  | 1            | Application_ApplicationId           | A         | 190093      | NULL     |        |      | BTREE      |         |
| AA    | 1          | AA_FKIndex2  | 1            | Applicant_ApplicantId               | A         | 190093      | NULL     |        |      | BTREE      |         |
| AA    | 1          | AA_FKIndex3  | 1            | Address_AddressId                   | A         | 190093      | NULL     |        |      | BTREE      |         |
| AA    | 1          | AA_FKIndex4  | 1            | C_CId                               | A         | 190093      | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | E_Index      | 1            | E                                   | A         | 190093      | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex5  | 1            | C_NameId                            | A         | 5           | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex6  | 1            | A_NameId                            | A         | 5           | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex7  | 1            | P_AddressId                         | A         | 21121       | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex8  | 1            | In_AddressId                        | A         | 5           | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex9  | 1            | Im_NameId                           | A         | 5           | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex10 | 1            | In_NameId                           | A         | 49          | NULL     |        | YES  | BTREE      |         |
+-------+------------+--------------+--------------+-------------------------------------+-----------+-------------+----------+--------+------+------------+---------+

Denne linken forteller at mange indexer gir trege Updates, men det er jo ikke AA som blir oppdatert.

Jeg tror det har med Cardinality å gjøre. OPTIMIZE TABLE og ANALYZE TABLE gir tilfeldige kardinaliteter på indeksene til kolonne C_NameId og nedover. Alle disse har 2000-6000 unike verdier.

Det virker som Update går raskt når disse har høyere kardinalitet, men jeg får ikke alle til å ha høy kardinalitet samtidig!

Er det mulig?

FORCE INDEX e.l. kan vel ikke hjelpe meg her?

Når kardinaliteten er høy blir updaten ferdig på ca. 5 sekunder, når den er lav (5-6) tar det timesvis (vet ikke om den blir ferdig).

Denne linken var interessant, men hjalp meg ikke i mål.

Select-delen av spørringen er rask. Kan det hjelpe å omforme spørringen a la dette?

Her er forresten Name-tabellen:

mysql> desc Name; show index from Name;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| NameId     | int(10) unsigned | NO   | PRI |         | auto_increment |
| FirstName  | varchar(50)      | NO   | MUL |         |                |
| MiddleName | varchar(50)      | YES  | MUL |         |                |
| LastName   | varchar(50)      | NO   | MUL |         |                |
| IsPerson   | tinyint(1)       | NO   |     |         |                |
+------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Name  | 0          | PRIMARY          | 1            | NameId      | A         | 872813      | NULL     |        |      | BTREE      |         |
| Name  | 1          | FirstName_Index  | 1            | FirstName   | A         | 4692        | NULL     |        |      | BTREE      |         |
| Name  | 1          | MiddleName_Index | 1            | MiddleName  | A         | 20781       | NULL     |        | YES  | BTREE      |         |
| Name  | 1          | LastName_Index   | 1            | LastName    | A         | 62343       | NULL     |        |      | BTREE      |         |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Address er veldig tilsvarende Name.

Endret 3. mars 2009 av drahcir

[siDDis]

Har du prøvd å analysere querien for å sjå kva som tar tid?

 

Om tabellen AA er SVÆR og name liten, så er det kanskje betre betre å bruke EXISTS

Prøv denne querien:

UPDATE Name SET FirstName='',LastName='' WHERE EXISTS (SELECT 1 FROM AA,TempApplication WHERE AA.Applicant_ApplicantId=TempApplicationId AND name.NameId = AA.NameId);

Endret 1. mars 2009 av siDDIs

[drahcir]

Har du prøvd å analysere querien for å sjå kva som tar tid?

Jo, delvis. Kan visst ikke kjøre EXPLAIN på UPDATE, bare SELECT. Se på dette:

mysql> show index from AA;
+-------+------------+--------------+--------------+-------------------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name                         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+--------------+--------------+-------------------------------------+-----------+-------------+----------+--------+------+------------+---------+
| AA    | 0          | PRIMARY      | 1            | Application_ApplicationId           | A         | 190405      | NULL     |        |      | BTREE      |         |
| AA    | 0          | PRIMARY      | 2            | Applicant_ApplicantId               | A         | 190405      | NULL     |        |      | BTREE      |         |
| AA    | 1          | AA_FKIndex1  | 1            | Application_ApplicationId           | A         | 190405      | NULL     |        |      | BTREE      |         |
| AA    | 1          | AA_FKIndex2  | 1            | Applicant_ApplicantId               | A         | 190405      | NULL     |        |      | BTREE      |         |
| AA    | 1          | AA_FKIndex3  | 1            | Address_AddressId                   | A         | 190405      | NULL     |        |      | BTREE      |         |
| AA    | 1          | AA_FKIndex4  | 1            | C_CId                               | A         | 190405      | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | E_Index      | 1            | E                                   | A         | 190405      | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex5  | 1            | C_NameId                            | A         | 27200       | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex6  | 1            | A_NameId                            | A         | 27200       | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex7  | 1            | P_AddressId                         | A         | 99          | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex8  | 1            | In_AddressId                        | A         | 6           | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex9  | 1            | Im_NameId                           | A         | 6           | NULL     |        | YES  | BTREE      |         |
| AA    | 1          | AA_FKIndex10 | 1            | In_NameId                           | A         | 6           | NULL     |        | YES  | BTREE      |         |
+-------+------------+--------------+--------------+-------------------------------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> explain SELECT Im_NameId FROM AA,TempApplication WHERE AA.Applicant_ApplicantId=TempApplicationId;
+----+-------------+-----------------+-------+---------------+-------------+---------+-----------------------------------+-------+-------------+
| id | select_type | table           | type  | possible_keys | key         | key_len | ref                               | rows  | Extra       |
+----+-------------+-----------------+-------+---------------+-------------+---------+-----------------------------------+-------+-------------+
| 1  | SIMPLE      | TempApplication | index | PRIMARY       | PRIMARY     | 4       |                                   | 29099 | Using index |
| 1  | SIMPLE      | AA              | ref   | AA_FKIndex2   | AA_FKIndex2 | 4       | TempApplication.TempApplicationId | 1     |             |
+----+-------------+-----------------+-------+---------------+-------------+---------+-----------------------------------+-------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT count(Im_NameId) FROM AA,TempApplication WHERE AA.Applicant_ApplicantId=TempApplicationId;
+------------------+
| count(Im_NameId) |
+------------------+
| 0                |
+------------------+
1 row in set (5.64 sec)

mysql> UPDATE Name SET FirstName = '', LastName = '' WHERE NameId IN (SELECT Im_NameId FROM AA,TempApplication WHERE AA.Applicant_ApplicantId=TempApplicationId);

Som du ser tilsier alt at selve spørringen burde gå raskt. Den siste linja (UPDATE) bruker svært lang tid. Da jeg gjorde det på C_Name og A_Name gikk det raskt. Prøvde din spørring også, men det går like sakte. Tror som sagt at det har med indeks-kardinaliteten å gjøre, men vet ikke hva jeg kan gjøre for å løse det.

[siDDis]

er name tabellen MyISAM? Kan det være noko med at den låser heile tabellen når du skriver til den?

[drahcir]

er name tabellen MyISAM? Kan det være noko med at den låser heile tabellen når du skriver til den?

Nei, den er også InnoDB.

 

Jeg tror jeg løste det ved å endre spørringen til:

UPDATE Name SET FirstName = '', LastName = '' WHERE NameId IN (
SELECT Im_NameId FROM AA STRAIGHT_JOIN TempApplication ON 
AA.Applicant_ApplicantId=TempApplicationId);

Eller man kan bruke:

UPDATE Name INNER JOIN AA ON NameId=Im_NameId INNER JOIN TempApplication ON 
Applicant_ApplicantId=TempApplicationId SET FirstName = '', LastName = '';

Jeg syntes det var merkelig at det skulle ta så lang tid selv om antallet rader var 0 (forrige post), så det er SELECT-delen som var problemet, ikke UPDATE, selv om SELECT-en oppførte seg bra når den stod alene. Takk for hjelpen uansett!