Bug #59809

Create better indexes for EM

Added by Stefan Froemken about 1 year ago. Updated 12 months ago.

Status:Resolved Start date:2014-06-23
Priority:Should have Due date:
Assigned To:Stefan Froemken % Done:

100%
Category:Extension Manager Spent time: -
Target version:next-patchlevel
TYPO3 Version:6.2 Is Regression:No
PHP Version:5.4 Sprint Focus:
Complexity:easy

Description

Hello Core-Team,

I have changed an index for em. Here are the result (before and after)

|        3 | 0.49219400 | SELECT SQL_NO_CACHE extension_key, max(integer_version) as maxintversion FROM tx_extensionmanager_domain_model_extension WHERE repository=1 GROUP BY extension_key    |
|        4 | 0.17256100 | SELECT SQL_NO_CACHE extension_key, max(integer_version) as maxintversion FROM tx_extensionmanager_domain_model_extension WHERE repository=1 GROUP BY extension_key    |

In general I would prefer to remove or move col "repository" to the end of an index

Stefan

Related issues

related to Core - Bug #59824: Better index for determineSiteRoot Resolved 2014-06-23

Associated revisions

Revision 81cee53d
Added by Stefan Froemken 12 months ago

[BUGFIX] EM using bad indexes

The extensionmanager uses bad indexes. It is better
to move col repository to the the end of the indexes.

Resolves: #59809
Releases: 6.3,6.2
Change-Id: I0bb8d5cdfc8591bb35485b204ad5e8dafd1b4d16
Reviewed-on: http://review.typo3.org/31080
Reviewed-by: Oliver Klee <>
Reviewed-by: Marcin Sągol <>
Reviewed-by: Tymoteusz Motylewski <>
Tested-by: Tymoteusz Motylewski <>
Reviewed-by: Markus Klein <>
Tested-by: Markus Klein <>

Revision 85c8b9f3
Added by Stefan Froemken 12 months ago

[BUGFIX] EM using bad indexes

The extensionmanager uses bad indexes. It is better
to move col repository to the the end of the indexes.

Resolves: #59809
Releases: 6.3,6.2
Change-Id: I0bb8d5cdfc8591bb35485b204ad5e8dafd1b4d16
Reviewed-on: http://review.typo3.org/32334
Reviewed-by: Markus Klein <>
Tested-by: Markus Klein <>

History

#1 Updated by Stefan Froemken about 1 year ago

And another one

change review_state with current_version:

|        1 | 0.00045700 | SELECT  COUNT(*) FROM tx_extensionmanager_domain_model_extension  WHERE (tx_extensionmanager_domain_model_extension.current_version = '1' AND tx_extensionmanager_domain_model_extension.review_state >= '1') |
|        2 | 0.01897600 | SELECT  COUNT(*) FROM tx_extensionmanager_domain_model_extension  WHERE (tx_extensionmanager_domain_model_extension.current_version = '1' AND tx_extensionmanager_domain_model_extension.review_state >= '1') |
|        3 | 0.00029400 | SELECT  COUNT(*) FROM tx_extensionmanager_domain_model_extension  WHERE (tx_extensionmanager_domain_model_extension.current_version = '1' AND tx_extensionmanager_domain_model_extension.review_state >= '1') |

1 = current
2 = without any index
3 = modified index

Stefan

#2 Updated by Gerrit Code Review about 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/31080

#3 Updated by Markus Klein about 1 year ago

Wow, impressive. Do you have a technical reasoning why switching the order of the index, helps so much?

#4 Updated by Stefan Froemken about 1 year ago

It is absolutely necessary to reduce the amount of selected rows as fast as you can within your index. In your case "repository" was the first field of index, but in standard environment all rows have repository=1. So after processing 32.000 records we have again 32.000 records. And that's the problem.

Please have also a look onto col "rows" in EXPLAIN of this statement. As smaller it is as faster the query is.

#5 Updated by Markus Klein about 1 year ago

Gotcha now! Thx.

#6 Updated by Nicole Cordes about 1 year ago

Could you explain what you did to get that query!? I don't get it and IMHO the change slows down the list view of the extension manager.

#7 Updated by Markus Klein about 1 year ago

I didn't measure it, but after applying the patch, updating the table and optimizing it, I had the feeling it is faster.

#8 Updated by Markus Klein about 1 year ago

According to http://en.wikipedia.org/wiki/Database_index#Column_order this really makes sense.
If we happen to have more than one repository though, we will loose speed again.

Thinking about this again, I even suggest to simply drop the repository from the index, this will also minimize the index size.

#9 Updated by Stefan Froemken about 1 year ago

I have added following line to my.cnf

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 0
log-queries-not-using-indexes

Then analyze this file with "mysqldumpslow". F.E:

./mysqldumpslow -t 50 -s r -a -r /var/log/mysql/mysql-slow.log

Stefan

#10 Updated by Gerrit Code Review 12 months ago

Patch set 1 for branch TYPO3_6-2 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32334

#11 Updated by Stefan Froemken 12 months ago

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

Also available in: Atom PDF