MySQL Forums
Forum List  »  Performance

Slow SELECT on two tables
Posted by: Stefan Mueller
Date: March 01, 2009 04:26PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow SELECT on two tables
3743
March 01, 2009 04:26PM
1844
March 01, 2009 09:23PM
1912
March 02, 2009 05:02PM
1776
March 02, 2009 09:14PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.