SQL query on large tables first then slow

Question

query returns quite slowly.. Do you know what should be done to improve the performance? Thanks

$where[] = "FIND_IN_SET(:pid, users.parents)";

$args['books'] = $this->query("SELECT books.*, users.user,
(SELECT COUNT(id) FROM {$this->prefix}books_best WHERE book_id = books.id) AS book_count,
(SELECT COUNT(id) FROM {$this->prefix}books_best WHERE book_id = books.id AND status > 1) AS result_count
FROM {$this->prefix}books AS books
INNER JOIN {$this->prefix}users AS users ON users.id = books.user_id
".(isset($_REQUEST['team']) && !empty($_REQUEST['team']) ? "
    INNER JOIN {$this->prefix}books_best AS tcb ON tcb.book_id = books.id
    INNER JOIN {$this->prefix}book_played AS tm ON tm.id = tcb.book_id AND (tm.home LIKE :team OR tm.away LIKE :team)
" : "")."
".(isset($_REQUEST['book_id']) && !empty($_REQUEST['book_id']) ? "
    INNER JOIN {$this->prefix}books_best AS mcb ON mcb.book_id = books.id AND mcb.book_id = ".intval($_REQUEST['book_id'])."
" : "")."
".(count($array) > 0 ? "WHERE ".implode(" AND ", $where) : "")."
ORDER BY {$orders[$orderable['type']]} {$orderable['order']}
", $array, array('counter' => 'books.id', 'limit' => $_REQUEST['limit'] ?? 25));
$args['pagination'] = $this->pagination();

Maybe this can help you: https://stackoverflow.com/questions/3002605/how-do-i-add-indices-to-mysql-tables — Alberto Moro, Dec 10 '19 at 07:49
use necessary indexes and foreign keys to enhance the query performances — Tharindu, Dec 10 '19 at 07:56
It might be useful to post the actual query, as received by the server — Strawberry, Dec 10 '19 at 08:13

ScaisEdge · Accepted Answer · 2019-12-10T10:30:01.397

Instead of two select for each row you could use a join with as subquery for both the count this subquery is executed just one time (and not for each row)

  select book_id
    , COUNT(id) book_count
    , sum( case when id is not null and status > 1 then 1 else 0 end) result_count
  FROM {$this->prefix}books_best
  group by book_id

For the table {$this->prefix}books_best be sure you have a

   composite index on columns (book_id, status)

based on this assumption you could refactor you query as

   SELECT books.*, users.user,
    t.book_count,
   t.result_count
  FROM {$this->prefix}books AS books
  INNER JOIN  (
      select book_id
        , COUNT(id) book_count
        , sum( case when id is not null and status > 1  then 1 else 0 end) result_count
      FROM {$this->prefix}books_best
      group by book_id
  ) t on t.book_id = books.id
  INNER JOIN {$this->prefix}users AS users ON users.id = books.user_id
  .....

Thank you very, very much for your interest ... I get an error.SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') INNER JOIN ( select book_id , COUNT(id) book_count , sum( case when id is not null and status > 1) then 1 else 0 end) result_count FROM {$this->prefix}books_best group by book_id ) <<< — beytullah kantar, Dec 10 '19 at 09:12

SQL query on large tables first then slow

1 Answers1