[mikexxl]

was ich gemachr habe:
habe tabelle tab1 und tab2 neu erstellt, x als VARCHAR 50 und index

tab 1 hat 2 und x spalten, tab 2 hat 11 und x spalten
Einspielen der Daten
mysql> insert into `db`.`tab1` (a,b,x)
-> select a,b,
-> CONCAT(a,' ',b) AS x
-> from `db`.`quelle`;
Query OK, 14372615 rows affected (6 hours 38,35 sec)
Records: 14372615 Duplicates: 0 Warnings: 0

mysql> insert into `coxulto`.`tab2` (spalten,x)
-> select spalten,
-> CONCAT(r,' ',p) AS x
-> from `db`.`quelle2`;
Query OK, 13629550 rows affected, 1 warning (9 min 2,78 sec)
Records: 13629550 Duplicates: 0 Warnings: 1

Warum diese Zeitunterschiede??

dann deine Befehle ausgeführt:
mysql> EXPLAIN
-> SELECT COUNT(*)
-> FROM tab2
-> LEFT JOIN tab1
-> ON a.x = a1.x
-> WHERE a.x IS NULL;
+----+-------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------------------+
| 1 | SIMPLE | tab2 | index | NULL | x | 152 | NULL | 13629550 | Using index |
| 1 | SIMPLE | tab1 | ref | x | x | 152 | db.tab2.x | 10 | Using where; Using index; Not exists |
+----+-------------+-------+-------+---------------+---------+---------+--------------------+----------+--------------------------------------+
2 rows in set (0,03 sec)

mysql> SELECT COUNT(*)
-> FROM a1
-> LEFT JOIN a
-> ON a.Telefon = a1.Telefon
-> WHERE a.Telefon IS NULL;
+----------+
| COUNT(*) |
+----------+
| 643384 |
+----------+
1 row in set (24 min 59,76 sec)

mysql> show global variables LIKE '%buffer%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 16777216 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 8192 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 524288 |
| sql_buffer_result | OFF |
+------------------------------+----------+
14 rows in set (0,02 sec)

Nur zum Verständnis, warum left join und nicht right join?

Kannst Du mir erklären, was ich da gerade gemacht habe?

Danke
MikeXXL