I have a ajax endpoint where I'm joining around 10 tables to get the necessary information, at the moment, this query is easily taking over 20 seconds to run (24.08 on a test run). The problem with this is that joins are generally quite expensive, but because we don't define proper foreign key restraints in our database schema definitions, the linking bigintegers that we used aren't indexed.
E.g.
$w->db->get('timelog'>leftJoin("task on task.id = timelog.object_id and timelog.object_class = \"Task\"")->leftJoin("task_group on task_group.id = task.task_group_id")->where('timelog.is_deleted", 0)->fetchAll();
In the above example, but default we don't have indexes on the timelog.object_id and the task.task_group_id so this query will take a lot longer than it would if we indexed these columns.
After adding the indexes in my 10 table join query, the 24.08 second run now runs at ~750ms. That's a speed increase by a factor of 32. We should introduce indexing on all biginteger columns, the only table we don't really have to do this on is the audit table.
I have a ajax endpoint where I'm joining around 10 tables to get the necessary information, at the moment, this query is easily taking over 20 seconds to run (24.08 on a test run). The problem with this is that joins are generally quite expensive, but because we don't define proper foreign key restraints in our database schema definitions, the linking bigintegers that we used aren't indexed.
E.g.
In the above example, but default we don't have indexes on the timelog.object_id and the task.task_group_id so this query will take a lot longer than it would if we indexed these columns.
After adding the indexes in my 10 table join query, the 24.08 second run now runs at ~750ms. That's a speed increase by a factor of 32. We should introduce indexing on all biginteger columns, the only table we don't really have to do this on is the audit table.