MySQL Forums :: Performance :: Slow SELECT on two tables


Advanced Search

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 3687 Stefan Mueller 03/01/2009 04:26PM
Re: Slow SELECT on two tables 1832 Rick James 03/01/2009 09:23PM
Re: Slow SELECT on two tables 1901 Stefan Mueller 03/02/2009 05:02PM
Re: Slow SELECT on two tables 1767 Rick James 03/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.