Bug #59809

Create better indexes for EM

Added by Stefan Froemken almost 7 years ago. Updated over 2 years ago.

Status:
Closed
Priority:
Should have
Category:
Extension Manager
Target version:
Start date:
2014-06-23
Due date:
% Done:

100%

Estimated time:
TYPO3 Version:
6.2
PHP Version:
5.4
Tags:
Complexity:
easy
Is Regression:
No
Sprint Focus:

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 TYPO3 Core - Bug #59824: Better index for determineSiteRootClosedStefan Froemken2014-06-23

Actions
#1

Updated by Stefan Froemken almost 7 years 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 almost 7 years 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 almost 7 years ago

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

#4

Updated by Stefan Froemken almost 7 years 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 almost 7 years ago

Gotcha now! Thx.

#6

Updated by Nicole Cordes almost 7 years 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 almost 7 years 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 almost 7 years 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 almost 7 years 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 over 6 years 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 over 6 years ago

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

Updated by Benni Mack over 2 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF