without the index on keywords.codice:
mysql> show index from keywords;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| keywords | 0 | PRIMARY | 1 | keyword | A
| NULL | NULL | NULL | |
| keywords | 0 | PRIMARY | 2 | codice | A
| 437735 | NULL | NULL | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
2 rows in set (0.06 sec)
mysql> show index from articoli;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| articoli | 0 | PRIMARY | 1 | codice | A
| 412345 | NULL | NULL | |
| articoli | 1 | marca | 1 | marca | A
| 2 | NULL | NULL | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
2 rows in set (0.16 sec)
mysql> select count(*) from keywords where keyword IN ('tubo');
+----------+
| count(*) |
+----------+
| 31210 |
+----------+
1 row in set (4.12 sec)
mysql> select count(distinct codice),count(*) from keywords;
+------------------------+----------+
| count(distinct codice) | count(*) |
+------------------------+----------+
| 465128 | 677829 |
+------------------------+----------+
1 row in set (1 min 0.32 sec)
Adding the index on keywords.codice:
mysql> alter table keywords add index(codice);
Query OK, 677829 rows affected (1 min 8.95 sec)
Records: 677829 Duplicates: 0 Warnings: 0
mysql> shows index from keywords;
ERROR 1064: You have an error in your SQL syntax near 'shows index from
keywords' at line 1
mysql> show index from keywords;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| keywords | 0 | PRIMARY | 1 | keyword | A
| NULL | NULL | NULL | |
| keywords | 0 | PRIMARY | 2 | codice | A
| 437735 | NULL | NULL | |
| keywords | 1 | codice | 1 | codice | A
| 437735 | NULL | NULL | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
3 rows in set (0.02 sec)
mysql> show index from articoli;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| articoli | 0 | PRIMARY | 1 | codice | A
| 412345 | NULL | NULL | |
| articoli | 1 | marca | 1 | marca | A
| 2 | NULL | NULL | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
2 rows in set (0.13 sec)
mysql> select count(*) from keywords where keyword IN ('tubo');
+----------+
| count(*) |
+----------+
| 31210 |
+----------+
1 row in set (2.04 sec)
mysql> select count(distinct codice),count(*) from keywords;
+------------------------+----------+
| count(distinct codice) | count(*) |
+------------------------+----------+
| 465128 | 677829 |
+------------------------+----------+
1 row in set (1 min 21.00 sec)
Thanks,
Nico
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
