Bug #55181

Epic #55070: Workpackages

Epic #55065: WP: Overall System Performance (Backend and Frontend)

Task #55179: Optimize SQL Performance

Speedup domain redirection lookup

Added by Ingo Schmitt over 1 year ago. Updated over 1 year ago.

Status:Resolved Start date:2014-01-20
Priority:Should have Due date:
Assigned To:Ingo Schmitt % Done:

100%
Category:Performance Spent time: 2.00 hours
Target version:6.2.0 Estimated time:2.00 hours
TYPO3 Version:6.2 Is Regression:No
PHP Version: Sprint Focus:
Complexity:

Description

The get sysDomainCache detects if a domain is redirected by issuing the query:
Count: 125 Time=0.00s (0s) Lock=0.00s (0s) Rows=262.0 (32750), webuser[webuser]@localhost
SELECT pid, domainName, forced FROM sys_domain WHERE redirectTo='S' AND sys_domain.hidden=N ORDER BY sorting ASC
TypoScriptFrontendController->getSysDomainCache()

There is no Database key on hidden and redirectTo. By setting a index the execution would be speeded up.

Associated revisions

Revision 84543545
Added by Ingo Schmitt over 1 year ago

[Bugfix] Add Index on sys_domain

TYPO3 checks if a redirect is needed for a domain by issuing a
select on sys_domain. In the where clause the columns redirectTo
and hidden are used. These columns have no index, thus the
select is slow.

This patch adds the combined key to the sql definition.

Resolves: #55181
Releases: 6.2
Change-Id: I7d7463e0d3e6abb53c4b0fccf205e4b1eb867878
Reviewed-on: https://review.typo3.org/27167
Reviewed-by: Anja Leichsenring
Tested-by: Anja Leichsenring
Reviewed-by: Benjamin Mack
Tested-by: Benjamin Mack

History

#1 Updated by Gerrit Code Review over 1 year ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/27167

#2 Updated by Ingo Schmitt over 1 year ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#3 Updated by Steffen Müller over 1 year ago

I went through the query and tried to reproduce the speed enhancements with approx. 3000 rows of sample data.

The index seems never to be used:

mysql> EXPLAIN SELECT pid, domainName, forced FROM sys_domain WHERE redirectTo='http://foobar/' AND hidden=0 ORDER BY sorting ASC;
+----+-------------+------------+------+-------------------+------+---------+------+------+-----------------------------+
| id | select_type | table      | type | possible_keys     | key  | key_len | ref  | rows | Extra                       |
+----+-------------+------------+------+-------------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | sys_domain | ALL  | redirectTo_hidden | NULL | NULL    | NULL | 3032 | Using where; Using filesort |
+----+-------------+------------+------+-------------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

Can you reproduce this or otherwise provide some more details about your test setup where the index is used?

#4 Updated by Ingo Schmitt over 1 year ago

  • Estimated time changed from 0.50 to 2.00

Also available in: Atom PDF