Documents missing table indexes

Idea created by Jonathan Cutting on Oct 29, 2015
    Active
    Score15
    • Jonathan Cutting
    • Harald Kuske
    • Mark Willert
    The documents and document_revisions tables have fields referenced in the list query that refer to id type fields not backed by indexes. See the query:

    SELECT
    jt0_assigned_user_link.first_name assigned_user_name__first_name,
    jt0_assigned_user_link.last_name assigned_user_name__last_name,
    documents.team_set_id team_set_id,
    jt1_team_link.name team_name,
    documents.active_date active_date,
    documents.exp_date exp_date,
    documents.category_id category_id,
    documents.subcategory_id subcategory_id,
    documents.status_id status_id,
    jt2_related_docs.document_name related_doc_name,
    jt3_revisions.revision related_doc_rev_number,
    documents.is_template is_template,
    documents.template_type template_type,
    documents.assigned_user_id assigned_user_id,
    documents.related_doc_id related_doc_id,
    documents.related_doc_rev_id related_doc_rev_id,
    documents.document_name document_name,
    documents.doc_type doc_type,
    documents.id id,
    documents.date_modified date_modified,
    documents.created_by created_by
    FROM
    documents
    INNER JOIN (
    SELECT
      tst.team_set_id
    FROM
      team_sets_teams tst
    INNER JOIN team_memberships team_memberships ON tst.team_id = team_memberships.team_id
    AND team_memberships.user_id = '87fe1203-c63e-a730-e8ea-51d147a93046'
    AND team_memberships.deleted = 0
    GROUP BY
      tst.team_set_id
    ) documents_tf ON documents_tf.team_set_id = documents.team_set_id
    LEFT JOIN users jt0_assigned_user_link ON (
    documents.assigned_user_id = jt0_assigned_user_link.id
    AND jt0_assigned_user_link.deleted = 0
    )
    LEFT JOIN teams jt1_team_link ON (
    documents.team_id = jt1_team_link.id
    AND jt1_team_link.deleted = 0
    )
    LEFT JOIN documents jt2_related_docs ON (
    documents.id = jt2_related_docs.related_doc_id
    AND jt2_related_docs.deleted = 0
    )
    LEFT JOIN document_revisions jt3_revisions ON (
    documents.id = jt3_revisions.document_id
    AND jt3_revisions.deleted = 0
    )
    WHERE
    documents.deleted = 0
    ORDER BY
    documents.date_modified DESC
    LIMIT 0,
    21
    which references documents.related_doc_id and document_revisions.document_id. Adding indexes to these fields returns the above query in 0.008 seconds on a system with 15,000 documents. Without the indexes the query still hadn't returned after 5 minutes.