Issue Feature Closed

#100296Add DB Index to sys_news

Stefan Froemken 03/24/23, 21:06 updated 03/27/23, 20:10

Description

Hello,

the query of sys_news is not using index. With an index you can “speed up” the query from 0.004 to 0.0005 seconds.

Stefan

Reviews 1

History 24

  1. Gerrit Code Review pushed patch set 1 for main to change 78252 status New Under Review 03/24/23, 21:13

  2. Stefan Froemken commented 03/24/23, 21:15

    It’s this query:

    SELECT
      `uid`,
      `title`,
      `content`,
      `crdate`
    FROM
      `sys_news`
    WHERE
      (
        (`sys_news`.`deleted` = 0)
        AND (`sys_news`.`hidden` = 0)
        AND (
          `sys_news`.`starttime` <= 1679690460
        )
        AND (
          (
            (`sys_news`.`endtime` = 0)
            OR (
              `sys_news`.`endtime` > 1679690460
            )
          )
        )
      )
    ORDER BY
      `crdate` DESC;
  3. Gerrit Code Review pushed patch set 2 for main to change 78252 03/24/23, 22:04

  4. Stefan Froemken commented and changed status Under Review Closed 03/27/23, 20:10

    The columns starttime, endtime, hidden and deleted are in 98% always 0. So the QueryParser decides: reading all data from disc is faster than reading and checking values from index. As no index is used now, it also make no sense to create an index on just crdate.