SQL Query is really slow, how to improve?

Question

EDIT: It seems the problem lies in the help_file table, which contains the actual binary data of the .pdf files which is being loaded everytime the table is selected, is there any way to circumvent this? I don't want to load all the data everytime.

I'm not that proficient in run-time effiency of SQL-Queries, but this query is very slow and takes about two seconds to execute.

SELECT
  id,
  display_name,
  NAME,
  document_id,
  chapter,
  aenderung_datum,
  DATA,
  file_attached,
  right_group,
  role,
  partner,
  sequence_nr
FROM
  (SELECT
    hf.id,
    hf.display_name,
    hf.name,
    hf.document_id,
    hf.aenderung_datum,
    DATA,
    LENGTH(DATA) > 0 AS file_attached,
    GROUP_CONCAT(
      DISTINCT (
        IF(
          b.name IS NULL,
          a.right_group_id,
          b.name
        )
      )
      ORDER BY a.right_group_id
    ) AS right_group,
    GROUP_CONCAT(
      DISTINCT (
        IF(
          role.name IS NULL,
          role_id,
          role.name
        )
      )
      ORDER BY role_id
    ) AS role,
    GROUP_CONCAT(
      DISTINCT (IF(ch.name IS NULL, '-', ch.name))
      ORDER BY a.chapter_id
    ) AS chapter,
    GROUP_CONCAT(
      DISTINCT (
        IF(c.name IS NULL, partner_id, c.name)
      )
      ORDER BY partner_id
    ) AS partner,
    GROUP_CONCAT(
      DISTINCT a.sequence_nr
      ORDER BY a.sequence_nr
    ) AS sequence_nr
  FROM
    db_release_osp.help_file hf
    LEFT JOIN db_release_osp.help_file_assign AS a
      ON help_file_id = hf.id
    LEFT JOIN cdb_admin_osp.right_group AS b
      ON a.right_group_id = b.id
    LEFT JOIN cdb_admin_osp.role
      ON a.role_id = role.id
    LEFT JOIN db_release_osp.help_chapter AS ch
      ON a.chapter_id = ch.id
    LEFT JOIN cdb_osp_admin.partner AS c
      ON partner_id = c.id
  GROUP BY hf.id
  ORDER BY hf.id) AS overview_table

Are there any big flaws im overlooking? How can i manage to lower the time it takes?

I guess using temporary tables could be a solution.

Answer 1

I suspect that joining tables with large binary columns consumes lots of memory. I have moved DATA, file_attached, and aenderung_datum in a separate join clause.

SELECT
  overview_table.id,
  display_name,
  NAME,
  document_id,
  chapter,
  aenderung_datum,
  DATA,
  file_attached,
  right_group,
  role,
  partner,
  sequence_nr
FROM
  (SELECT
    hf.id,
    hf.display_name,
    hf.name,
    hf.document_id,
    GROUP_CONCAT(
      DISTINCT (
        IF(
          b.name IS NULL,
          a.right_group_id,
          b.name
        )
      )
      ORDER BY a.right_group_id
    ) AS right_group,
    GROUP_CONCAT(
      DISTINCT (
        IF(
          role.name IS NULL,
          role_id,
          role.name
        )
      )
      ORDER BY role_id
    ) AS role,
    GROUP_CONCAT(
      DISTINCT (IF(ch.name IS NULL, '-', ch.name))
      ORDER BY a.chapter_id
    ) AS chapter,
    GROUP_CONCAT(
      DISTINCT (
        IF(c.name IS NULL, partner_id, c.name)
      )
      ORDER BY partner_id
    ) AS partner,
    GROUP_CONCAT(
      DISTINCT a.sequence_nr
      ORDER BY a.sequence_nr
    ) AS sequence_nr
  FROM
    db_release_osp.help_file hf
    LEFT JOIN db_release_osp.help_file_assign AS a
      ON help_file_id = hf.id
    LEFT JOIN cdb_admin_osp.right_group AS b
      ON a.right_group_id = b.id
    LEFT JOIN cdb_admin_osp.role
      ON a.role_id = role.id
    LEFT JOIN db_release_osp.help_chapter AS ch
      ON a.chapter_id = ch.id
    LEFT JOIN cdb_osp_admin.partner AS c
      ON partner_id = c.id
  GROUP BY hf.id
  ORDER BY hf.id) AS overview_table
LEFT JOIN
  (SELECT
    id,
    DATA,
    LENGTH(DATA) > 0 AS file_attached,
    aenderung_datum
    FROM db_release_osp.help_file) AS data_table
  ON data_table.id = overview_table.id

Answer 2

First, your outer query is returning the exact results of the inner query with exception of the order of the fields (Chapter). I would just move the Chapter field into the expected position if you explicitly needed it that way, then remove the outer query which is redundant.

SELECT
      hf.id,
      hf.display_name,
      hf.name,
      hf.document_id,
      GROUP_CONCAT( DISTINCT (IF(ch.name IS NULL, 
         '-', ch.name))
         ORDER BY a.chapter_id ) AS chapter,
      hf.aenderung_datum,
      DATA,
      LENGTH(DATA) > 0 AS file_attached,
      GROUP_CONCAT( DISTINCT ( IF( b.name IS NULL,
         a.right_group_id, b.name )) 
         ORDER BY a.right_group_id ) AS right_group,
      GROUP_CONCAT( DISTINCT ( IF( role.name IS NULL,
         a.role_id, role.name ))
         ORDER BY a.role_id ) AS role,
      GROUP_CONCAT( DISTINCT ( IF(c.name IS NULL, 
         a.partner_id, c.name))
         ORDER BY a.partner_id ) AS partner,
      GROUP_CONCAT( DISTINCT a.sequence_nr 
         ORDER BY a.sequence_nr ) AS sequence_nr
   FROM
      db_release_osp.help_file hf
         LEFT JOIN db_release_osp.help_file_assign AS a
            ON hf.id = a.help_file_id
            LEFT JOIN cdb_admin_osp.right_group AS b
               ON a.right_group_id = b.id
            LEFT JOIN cdb_admin_osp.role
               ON a.role_id = role.id
            LEFT JOIN db_release_osp.help_chapter AS ch
               ON a.chapter_id = ch.id
            LEFT JOIN cdb_osp_admin.partner AS c
               ON partner_id = c.id
   GROUP BY 
      hf.id
   ORDER BY 
      hf.id

Next I would offer the following indexes on the tables to help make them "covering" indexes. This way, it can grab the id and name (or other parts) directly from the index without having to go to the raw data pages for the lookup table values.

table             index
help_file         (id)
right_group       (id, name )
role              (id, name )
help_chapter      (id, name )
partner           (id, name )
help_file_assign  (help_file_id, right_group_id, role_id, chapter_id, partner_id )
-- Note: Due to lack of alias on your left-join to the partner table
-- alias "c", it APPEARS the "Partner_ID" column is from your 
-- help_file_assign table.  If other, please adjust, but also fix the
-- alias reference for clarification.

If the performance is still hanging on you some, you might want to add one more keyword as this tells MySQL to run the query exactly as it has been presented.

SELECT STRAIGHT_JOIN  (rest of query)