Review main Abandoned

27247[TASK] BE: Only make SQL count calls on uid not *

Benni Mack CR 0 V 0 Easy +4 −2 updated 02/02/14, 14:09

Commit message

The Record List (list and page module) runs
over all TCA tables and checks if a table should
be listed.

The total query information is called for every table,
which is quite a lot. However, only the count number
on these TCA tables is important. That's why the call
to COUNT(*) is rewritten to COUNT(uid) which speeds
up these SQL calls.

Review

  • Code review CR 0
  • Verified V 0

Issues 1

Files 1

Review log 9

  1. Benni Mack wrote 02/01/14, 21:51

    Topic set to "performance".
  2. Mr. Jenkins commented on patch set 1 CR -1 V -1 02/01/14, 22:29

  3. Andreas Wolf voted on patch set 1 CR +1 02/01/14, 23:47

  4. Christian Kuhn commented on patch set 1 02/02/14, 00:39

    i think i've read that a count(*) query in mysql can be quicker than count(uid) ... did you measure something or is the patch just a guess, did i misunderstand the patch?
  5. Benni Mack commented on patch set 1 02/02/14, 08:58

    Hey christian, I have an installation with 91 TCA tables - that means 91 DB queries. I measured the list module before and after (yes, multiple times!) and had a drop of about 50% of this query (measured via XHProf on a live installation). Before: 77-81ms on 91x setTotalItems() After: 34-41ms on 91x setTotalItems()
  6. Oliver Klee voted on patch set 1 CR +1 02/02/14, 13:37

  7. Stefan Froemken commented on patch set 1 V -1 CR -1 02/02/14, 13:38

  8. Benni Mack commented on patch set 1 02/02/14, 14:08

    "MySQL 5.5 seems to be optimized in concern of COUNT(*) queries. At the same machine, for the table of ~120K records I’ve ~2.4 seconds for 5.1 and 0.08 seconds for 5.5 for “SELECT COUNT(*) FROM table” query." I think we can leave it then as is.
  9. Benni Mack abandoned the change 02/02/14, 14:09

    my conclusion - focus on having right indexes in where instead of COUNT(*) / COUNT(uid) changes.