Re: Slow SELECT on two tables

[Stefan Mueller]

Thanks for your response, Rick.

Quote

I would not be surprised if it took a few seconds. But 40s ??

Yes, it really is around 40s. I also expected the query to be run in a few seconds, but:

mysql> SELECT DISTINCT REPLACE(webfiles.filepath, product_files.filename, '') AS path FROM webfiles, product_files
    -> WHERE webfiles.filehash = product_files.filehash 
    -> AND webfiles.filepath LIKE CONCAT('%/', product_files.filename);
+---------------------------------------------------------+
| path                                                    |
+---------------------------------------------------------+
| /var/www/domain1/htdocs/wordpress/                      | 
+---------------------------------------------------------+
1 row in set (36,11 sec)

Quote

Perhaps innodb_buffer_pool_size is very low? It should be about 70% of _available_ RAM.

I don't know if my configuration is 100% correct. However, I've set innodb_buffer_pool_size to 1G, which I think is enough.
If it helps, for readability, you can find my full 'show variables' output on http://pastebin.com/m2496be94

Quote

I doubt if it would help, but please try changing

AND webfiles.filepath LIKE CONCAT('%/', product_files.filename);

to

AND SUBSTRING_INDEX(webfiles.filepath, '/', -1) = product_files.filename;

Thanks. I've been playing around with it a bit and it seems that it's faster on small queries and a little bit slower on large ones:

mysql> SELECT DISTINCT REPLACE(webfiles.filepath, product_files.filename, '') AS path FROM webfiles, product_files WHERE webfiles.filehash = product_files.filehash 
    -> AND SUBSTRING_INDEX(webfiles.filepath, '/', -1) = product_files.filename;
+---------------------------------------------------------+
| path                                                    |
+---------------------------------------------------------+
| /var/www/domain1/htdocs/wordpress/                      | 
+---------------------------------------------------------+
1 row in set (37,65 sec)

If it is too small, you may be thrashing the disk. Do you have any metrics on CPU and disk usage during this query?

I don't habe any real metrics, but what I've noticed is that the CPU goes up to 100% during the query. I/O is not worth mentioning, just a few Kb's of R/W.

I'm currentyl at a loss and don't know what why this query is taking so damn long. Please let me know if you're missing any addional useful information, and I will provide as much info as needed.

Any help is really appreciated.

Thanks again for your time!