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