Project

General

Profile

Actions

Bug #21979

closed

Caching framework sub-select is slow because mysql does not use indexes on outer query

Added by Christian Kuhn about 14 years ago. Updated over 13 years ago.

Status:
Closed
Priority:
Should have
Category:
Caching
Target version:
-
Start date:
2010-01-17
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
4.3
PHP Version:
5.2
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

Problem:
mysql is unable to use indexes for outer query if where clause is a sub select [1]. This makes tag-deletion in the caching framework with db backend very slow on bigger tables because mysql happily decides to perform a full table scan.

Solution:
Separate queries and do more logic with php.

Notes:
Running a production site with enabled caching framework frequently showed us slow queries (>1s) like those:
DELETE FROM cachingframework_cache_pages WHERE identifier IN (SELECT identifier FROM cachingframework_cache_pages_tags WHERE cachingframework_cache_pages_tags.tag = 'pageId_46');
Changing "DELETE FROM" to "EXPLAIN SELECT * FROM" showed us mysql does not use the defined index for the outer identifier field (handled rows = number of rows in table, possible keys NULL, no key used). After splitting the sub select into an own query no more slow queries occured in this part of the caching framework. Two single queries had a performance factor of 1000 (from 2-5 seconds to some milliseconds) on a cachingframework_cache_pages table with ~100k entries and ~2gig size.

The host is running debian stable with mysql 5.0.51, I would be interested if newer mysql version still suffer from this issue.

Warning: Test this with real data and big tables only. If there are not enough rows in a table, mysql might decide to ignore indexes completely anyway, even if they exist and could be used.

[1] http://www.mysqlperformanceblog.com/2006/08/31/derived-tables-and-views-performance/

(issue imported from #M13273)


Files

13273_01.diff (3.65 KB) 13273_01.diff Administrator Admin, 2010-01-17 19:45
13273_03.diff (3.89 KB) 13273_03.diff Administrator Admin, 2010-03-28 16:22
13273_03_testcase_fix.diff (957 Bytes) 13273_03_testcase_fix.diff Administrator Admin, 2010-03-28 16:22
Actions #1

Updated by jo about 14 years ago

I can confirm that. Once the tables are a bit larger, the queries are getting too slow. Using tha patch from C. Kuhn solved the issue for me (monitored via the slow-query log).

Actions #2

Updated by Christian Kuhn about 14 years ago

@jo: The patch is already pending in the core patch list and waiting for reviews to be commit ready. It would be great if you could post your findings to the appropriate thread there to get a formal feedback.

Actions #3

Updated by Christian Kuhn about 14 years ago

@jo: Please see http://typo3.org/teams/core/core-mailinglist-rules/ for more details on this topic.

Actions #4

Updated by Christian Kuhn about 14 years ago

Committed v3to:
- trunk rev. 7204
- TYPO3_4-3 rev. 7206

Committed testcase fix to:
- trunk rev. 7205
- TYPO3_4-3 rev. 7207

Actions

Also available in: Atom PDF