Hi,
I'm running into a significant performance issue when I query two tables with the statement below. The query takes ~40s which is quite long. Is there any way to optimize the query? I'm new to all of this so please bear with me :)
Running MySQL 5.0.77 on MacOSX 10.5.
The first table:
| webfiles | CREATE TABLE `webfiles` (
`id` int(11) NOT NULL auto_increment,
`filepath` varchar(4096) NOT NULL,
`filehash` binary(32) NOT NULL,
`system_id` int(11) default NULL,
`created_at` datetime default NULL,
`updated_at` datetime default NULL,
`webapp_id` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `system_id` (`system_id`),
KEY `webapp_id` (`webapp_id`),
KEY `filehash` (`filehash`)
) ENGINE=InnoDB AUTO_INCREMENT=16622 DEFAULT CHARSET=utf8 |
The second table:
| product_files | CREATE TABLE `product_files` (
`id` int(11) NOT NULL auto_increment,
`filename` varchar(4096) NOT NULL,
`filehash` binary(32) NOT NULL,
`created_at` datetime default NULL,
`updated_at` datetime default NULL,
PRIMARY KEY (`id`),
KEY `filehash` (`filehash`)
) ENGINE=InnoDB AUTO_INCREMENT=15035 DEFAULT CHARSET=utf8 |
The explain for the query:
mysql> EXPLAIN SELECT DISTINCT REPLACE(webfiles.filepath, product_files.filename, '') AS path FROM product_files, webfiles
-> WHERE webfiles.filehash = product_files.filehash
-> AND webfiles.filepath LIKE CONCAT('%/', product_files.filename);
+----+-------------+---------------+------+---------------+----------+---------+---------------------------------------+-------+-----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+----------+---------+---------------------------------------+-------+-----------------+
| 1 | SIMPLE | product_files | ALL | filehash | NULL | NULL | NULL | 16089 | Using temporary |
| 1 | SIMPLE | webfiles | ref | filehash | filehash | 32 | mb_development.product_files.filehash | 1 | Using where |
+----+-------------+---------------+------+---------------+----------+---------+---------------------------------------+-------+-----------------+
2 rows in set (0,00 sec)
Any help would be appreciated.
TIA