Bug #55891
closedInsert a CheckIfRecordExists in Listmodule
Added by Stefan Froemken over 10 years ago. Updated almost 7 years ago.
100%
Description
Hello Coreteam,
for now you check if records exists in table with COUNT. But on large tables this needs a lot of time. On one of our systems with 1,3 million records 0.3 seconds. I found out a larger SQL-Query which only takes the first col (uid) of a table and breaks further processing with LIMIT 1.
Stefan
Updated by Gerrit Code Review over 10 years ago
- Status changed from New to Under Review
Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/27554
Updated by Gerrit Code Review over 10 years ago
Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/27554
Updated by Gerrit Code Review over 10 years ago
Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/27554
Updated by Gerrit Code Review over 10 years ago
Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/27554
Updated by Stefan Froemken over 10 years ago
There are some people out there who told me that this patch adds an additional query here and an additional queries means an overhead. I don't think so.
Current:
You prepare some visual styles for table regardless if it contains values or not.
My Patch:
Stops preparing anything very early, with help of an additional, but very fast query.
Current:
If a table has records was currently checked with COUNT. As more records are in a table as slower such a query is. 1,3 million records = 0.3 seconds. A COUNT is faster for sure, but not as much: 1,3 million records = 0.24 seconds.
My Patch:
I create a query on ONE col and retrieve only the first row: SELECT uid FROM table WHERE deleted=0 + versioning LIMIT 1.
We don't make use of an Full Index Scan here like above. The bigger the index is, the longer COUNT needs.
Sure: My Table Scan is slower in normal cases. But I don't make use of ordering and my query can stop scanning with first found record immediately.
Such a query just needs 0.00085 seconds and is in this case 352 times faster.
I heard about overheads on grandmas website. OK...lets test it:
Here some profilings:4 | 0.00020800 | SELECT SQL_NO_CACHE COUNT FROM be_users WHERE deleted=0 |
5 | 0.00020975 | SELECT SQL_NO_CACHE COUNT FROM be_users WHERE deleted=0 |
6 | 0.00019525 | SELECT SQL_NO_CACHE uid FROM be_users WHERE deleted=0 LIMIT 1 |
11 | 0.00661575 | SELECT SQL_NO_CACHE COUNT FROM sys_log |
12 | 0.00526800 | SELECT SQL_NO_CACHE COUNT FROM sys_log |
13 | 0.00012050 | SELECT SQL_NO_CACHE uid FROM sys_log LIMIT 1 |
Current:
Makes use of COUNT on ALL tables
My Patch:
While current master needs 0.00661575 seconds to get amount of records in sys_log (see profiling), my patch can check 54 (0.00661575 / 0.00012050) tables for existing data. How many tables a little TYPO3 instance have? 40? 50? As you can see: There must be only ONE big table in TYPO3 and my patch is faster than the solution in current master. Also on very small websites.
Hope it helps
Stefan
Updated by Thomas Löffler over 10 years ago
Test on our system with really big tables:
Table: tx_aliasmultiply_url, 2.809.896 records
3.6249 | SELECT SQL_NO_CACHE COUNT FROM tx_aliasmultiply_url WHERE deleted=0 |
0.8926 | SELECT SQL_NO_CACHE COUNT FROM tx_aliasmultiply_url WHERE deleted=0 |
Table: tt_content, 155.816 records
0.4033 | SELECT SQL_NO_CACHE COUNT FROM tt_content WHERE deleted=0 |
0.3967 | SELECT SQL_NO_CACHE COUNT FROM tt_content WHERE deleted=0 |
Hope, this helps.
Unfortunately our MySQL servers shows no profiles, I used the time pMA showed me.
Updated by Stefan Froemken over 10 years ago
Thank you Thomas,
@all review: As you can see it needs nearly 4 seconds for ONE table and nearly 1 seconds for tt_content. So with each call on BE-Listmodule you have to wait very long for table rendering.
@thomas: It would be nice to show us the times of:
SELECT SQL_NO_CACHE uid FROM tt_content WHERE deleted=0 LIMIT 1
and
SELECT SQL_NO_CACHE uid FROM tx_aliasmultiply_url WHERE deleted=0 LIMIT 1
to see how much time can be won with my patch.
Stefan
Updated by Gerrit Code Review over 10 years ago
Patch set 6 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/27554
Updated by Gerrit Code Review over 10 years ago
Patch set 7 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/27554
Updated by Stefan Froemken over 10 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 9a63a585097aafebb9577cc3f8de253f30898238.
Updated by Riccardo De Contardi almost 7 years ago
- Status changed from Resolved to Closed