Bug #55891

Insert a CheckIfRecordExists in Listmodule

Added by Stefan Froemken about 7 years ago. Updated over 3 years ago.

Status:
Closed
Priority:
Should have
Category:
Performance
Target version:
Start date:
2014-02-11
Due date:
% Done:

100%

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

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


Related issues

Related to TYPO3 Core - Task #55568: BE: Only make SQL count calls on uid not *ClosedBenni Mack2014-02-01

Actions
#1

Updated by Gerrit Code Review about 7 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

#2

Updated by Gerrit Code Review about 7 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

#3

Updated by Gerrit Code Review about 7 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

#4

Updated by Gerrit Code Review about 7 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

#5

Updated by Stefan Froemken about 7 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

#6

Updated by Thomas Löffler about 7 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.

#7

Updated by Stefan Froemken about 7 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

#8

Updated by Gerrit Code Review about 7 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

#9

Updated by Gerrit Code Review about 7 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

#10

Updated by Stefan Froemken about 7 years ago

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

Updated by Riccardo De Contardi over 3 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF