{"action":"created","issue":{"url":"https://api.github.com/repos/dmitryd/typo3-realurl/issues/484","repository_url":"https://api.github.com/repos/dmitryd/typo3-realurl","labels_url":"https://api.github.com/repos/dmitryd/typo3-realurl/issues/484/labels{/name}","comments_url":"https://api.github.com/repos/dmitryd/typo3-realurl/issues/484/comments","events_url":"https://api.github.com/repos/dmitryd/typo3-realurl/issues/484/events","html_url":"https://github.com/dmitryd/typo3-realurl/issues/484","id":232222953,"number":484,"title":"[FEATURE] Speedup urldata queries","user":{"login":"christianfutterlieb","id":195543,"avatar_url":"https://avatars0.githubusercontent.com/u/195543?v=3","gravatar_id":"","url":"https://api.github.com/users/christianfutterlieb","html_url":"https://github.com/christianfutterlieb","followers_url":"https://api.github.com/users/christianfutterlieb/followers","following_url":"https://api.github.com/users/christianfutterlieb/following{/other_user}","gists_url":"https://api.github.com/users/christianfutterlieb/gists{/gist_id}","starred_url":"https://api.github.com/users/christianfutterlieb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/christianfutterlieb/subscriptions","organizations_url":"https://api.github.com/users/christianfutterlieb/orgs","repos_url":"https://api.github.com/users/christianfutterlieb/repos","events_url":"https://api.github.com/users/christianfutterlieb/events{/privacy}","received_events_url":"https://api.github.com/users/christianfutterlieb/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2017-05-30T11:32:01Z","updated_at":"2017-05-31T15:00:07Z","closed_at":null,"body":"Hi @dmitryd \r\n\r\nI'm currently upgrading a rather large TYPO3 installation (6.2 => 7.6), including an upgrade of realurl (obviously ;-)). I noticed a decrease of performance, analysis lead me to realurl, especially the queries to `tx_realurl_urldata`. Which was a surprise to me, because I never experienced such behaviour with realurl on other websites.\r\nDigging deeper, I took a look at the indexes on that table, which seemed completely ok to me (testing with e91193221f77ffb742d6c2140a1092a0e15c631c improved the lookup even more).\r\n\r\nThen: as far as I understand the index prefix specification (32) in `KEY pathq1 (rootpage_id,expire,original_url(32))`, it will cause mysql to add only the first 32 chars of `original_url` to the index. The installation in question hosts a large forum with few pages, but lots of different URLs produced by the forum plugin. When I did a lookup of the first 32 chars of `tx_realurl_urldata.original_url` it revealed, that almost every original_url starts with the same 32 chars:\r\n\r\n```\r\n+-------+----------------------------------+\r\n| count | ind_url                          |\r\n+-------+----------------------------------+\r\n| 85689 | id=1&tx_myext_forum%5Baction%5D= |\r\n|  ---- | cropped insignificant stuff here |\r\n|     1 | id=14                            |\r\n|     1 | id=13                            |\r\n|     1 | id=12                            |\r\n+-------+----------------------------------+\r\n```\r\nUsing this query:\r\n```mysql\r\nSELECT\r\n  COUNT(*) AS count,\r\n  SUBSTR(`original_url`,1,32) as ind_url\r\nFROM `tx_realurl_urldata`\r\nGROUP BY SUBSTR(`original_url`,1,32)\r\nORDER by count DESC\r\n```\r\n\r\nNow, finally, to my point: I think it was a very strong improvement for realurl, when it would store, say, a sha1 hash of the `original_url` in a separate `CHAR (40)` field and put the appropriate index on it, for example `KEY pathq_hash (rootpage_id,expire,original_url_hash(10))`. This would make the index file much smaller and speed up the lookup. The same could apply for `tx_realurl_urldata.speaking_url`.\r\n\r\nTo develop the thought even further: the fastest solution would be to create a new table with only a reference of `tx_realurl_urldata.uid` and the hash(es), apply the indexes and query only this table and join `tx_realurl_urldata` by `uid`. This approach is even then faster, when `speaking_url` holds short values, which is not the case with the above proposal.\r\n\r\nWhat do you think about this idea? When you like it, I'll be ready to put some work into this.\r\n\r\nRegards, Christian"},"comment":{"url":"https://api.github.com/repos/dmitryd/typo3-realurl/issues/comments/305214160","html_url":"https://github.com/dmitryd/typo3-realurl/issues/484#issuecomment-305214160","issue_url":"https://api.github.com/repos/dmitryd/typo3-realurl/issues/484","id":305214160,"user":{"login":"christianfutterlieb","id":195543,"avatar_url":"https://avatars0.githubusercontent.com/u/195543?v=3","gravatar_id":"","url":"https://api.github.com/users/christianfutterlieb","html_url":"https://github.com/christianfutterlieb","followers_url":"https://api.github.com/users/christianfutterlieb/followers","following_url":"https://api.github.com/users/christianfutterlieb/following{/other_user}","gists_url":"https://api.github.com/users/christianfutterlieb/gists{/gist_id}","starred_url":"https://api.github.com/users/christianfutterlieb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/christianfutterlieb/subscriptions","organizations_url":"https://api.github.com/users/christianfutterlieb/orgs","repos_url":"https://api.github.com/users/christianfutterlieb/repos","events_url":"https://api.github.com/users/christianfutterlieb/events{/privacy}","received_events_url":"https://api.github.com/users/christianfutterlieb/received_events","type":"User","site_admin":false},"created_at":"2017-05-31T15:00:07Z","updated_at":"2017-05-31T15:00:07Z","body":"Thanks for your reply. \r\n\r\n> Splitting the table may create consistency issues. TYPO3 does not support cascade delete, for example, so it is not easy to keep all tables in sync, especially when people use phpMyAdmin to remove data in them.\r\n\r\nAs realurl uses InnoDB engine, it would be easy to solve this with foreign key constraints. Afaik, TYPO3 does not support those in ext_tables.sql, so it would be necessary to set them up via ext_update. And: if people mess around by hand in the db tables, they might get other problems ;)\r\n\r\n> Mysql uses only one index per query, so if the data stays in one table, the index has to be complex\r\n> The longer the index, the less chances that mysql will use it because in such cases it usually thinks it can do the whole table scan in the same time\r\n\r\nI'm not sure if I understand you correct: the index would get shorter anyway when it uses `original_url_hash(10)` instead of `original_url(32)`, wouldn't it? But: let's not focus on this as I don't really see a solution with one table, please see below.\r\n\r\n> I would prefer to have a single table and improve the index. But if nothing helps, a separate table can be used to.\r\n\r\nWith one table only, there must be some additional logic in `DatabaseCache`. Namely to workaround the problem, that the proposed idea is significantly slower with short `original_url`s (you can check this for yourself using the sql script in the gist posted above). But I don't like that, a cache must be as straightforward as possible. \r\n\r\nWhat do you think of following solution: we introduce a switch in ext_conf_template.txt that en-/disables \"Use hashed urldata cache\" (or whatever naming) which is by default off. Enabling it would cause realurl to use a new cache class that extends `DatabaseCache`. Like this, the user can enable it if there is need for it. And the code can be transparently separated without switching in the cache-methods. And, nothing has to be changed with the current indexing of `tx_realurl_urldata`, as the new table would maintain its own index. The only \"overhead\" would be the possibly unused table and one single switch in `CacheFactory ` that decides which cache-class is used.\r\n\r\nAppreciate your answer, Christian"}}
{"actor":{"display_login":"christianfutterlieb"}}