TYPO3 Core

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

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

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.

Gotcha now! Thx.

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.

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

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.

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

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