Hi, I have the following tables :
CREATE TABLE articoli (
codice varchar(16) PRIMARY KEY,
descrizione varchar(255),
marca varchar(255) NOT NULL,
misure varchar(32),
peso float DEFAULT 0 NOT NULL,
disponibilita int,
timestamp int NOT NULL,
prezzo int,
INDEX marca(marca)
) TYPE=InnoDB;
(~ 500K records)
CREATE TABLE keywords (
keyword varchar(128) NOT NULL,
codice varchar(16) NOT NULL,
timestamp int NOT NULL,
soundekw varchar(32) NOT NULL,
PRIMARY KEY(keyword, codice),
) TYPE=InnoDB;
(~ 677K records).
and the following query:
SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice = k.codice
AND keyword IN ('tubo') GROUP BY a.codice HAVING count(k.keyword)=1 limit 1, 26;
that yields: 26 rows in set (11.95 sec)
explain SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice =
k.codice AND keyword IN ('tubo') GROUP BY a.codice HAVING
count(k.keyword)=1 limit 1, 26;
+-------+--------+---------------+---------+---------+----------+--------+------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+--------+---------------+---------+---------+----------+--------+------------------------------------------+
| k | range | PRIMARY | PRIMARY | 128 | NULL | 437735 |
where used; Using index; Using temporary |
| a | eq_ref | PRIMARY | PRIMARY | 16 | k.codice | 1 |
|
+-------+--------+---------------+---------+---------+----------+--------+------------------------------------------+
2 rows in set (4.21 sec)
If I add an index:
alter table keywords add index codice(codice);
'Query OK, 677829 rows affected (1 min 32.58 sec)
Records: 677829 Duplicates: 0 Warnings: 0'
and I rerun the same query it yields:
26 rows in set (1 min 33.77 sec)
almost 8 times slower!!
Explain says:
explain SELECT DISTINCT a.* FROM articoli a, keywords k WHERE a.codice =
k.codice AND keyword IN ('tubo') GROUP BY a.codice HAVING
count(k.keyword)=1 limit 1, 26;
+-------+------+----------------+--------+---------+----------+--------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+----------------+--------+---------+----------+--------+-------------------------+
| a | ALL | PRIMARY | NULL | NULL | NULL | 412345 |
Using temporary |
| k | ref | PRIMARY,codice | codice | 16 | a.codice | 1 |
where used; Using index |
+-------+------+----------------+--------+---------+----------+--------+-------------------------+
2 rows in set (0.00 sec)
As far as I remember keywords.codice shouldn't be of any use to speed up
the query because it's the second key of the PK, instead explain lists it
in possible_keys.
Please, can anyone advice me on how to make this query faster?
Thanks in advance,
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
