JohnT JohnT - 5 months ago 11
MySQL Question

Improve speed of MySQL query with 5 left joins

Working on a support ticketing system with not a lot of tickets (~3,000). To get a summary grid of ticket information, there are five LEFT JOIN statements on custom field table (j25_field_value) containing about 10,000 records. The query runs too long (~10 seconds) and in cases with a WHERE clause, it runs even longer (up to ~30 seconds or more).

Any suggestions for improving the query to reduce the time to run?

Four tables:


  • j25_support_tickets

    CREATE TABLE `j25_support_tickets` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `category_id` int(11) NOT NULL DEFAULT '0',
    `user_id` int(11) DEFAULT NULL,
    `email` varchar(50) DEFAULT NULL,
    `subject` varchar(255) DEFAULT NULL,
    `message` text,
    `modified_date` datetime DEFAULT NULL,
    `priority_id` tinyint(3) unsigned DEFAULT NULL,
    `status_id` tinyint(3) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id` (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=3868 DEFAULT CHARSET=utf8

  • j25_support_priorities

    CREATE TABLE `j25_support_priorities` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id` (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

  • j25_support_statuses

    CREATE TABLE `j25_support_statuses` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `id` (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

  • j25_field_value (id, ticket_id, field_id, field_value)

    CREATE TABLE `j25_support_field_value` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `ticket_id` int(11) DEFAULT NULL,
    `field_id` int(11) DEFAULT NULL,
    `field_value` tinytext,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=10889 DEFAULT CHARSET=utf8



Also, ran this:

SELECT LENGTH(field_value) len FROM j25_support_field_value ORDER BY len DESC LIMIT 1
note: the result = 38


The query:

SELECT DISTINCT t.id as ID
, (select p.title from j25_support_priorities p where p.id = t.priority_id) as Priority
, (select s.title from j25_support_statuses s where s.id = t.status_id) as Status
, t.subject as Subject
, t.email as SubmittedByEmail
, type.field_value AS IssueType
, ver.field_value AS Version
, utype.field_value AS UserType
, cust.field_value AS Company
, refno.field_value AS RefNo
, t.modified_date as Modified
FROM j25_support_tickets AS t
LEFT JOIN j25_support_field_value AS type ON t.id = type.ticket_id AND type.field_id =1
LEFT JOIN j25_support_field_value AS ver ON t.id = ver.ticket_id AND ver.field_id =2
LEFT JOIN j25_support_field_value AS utype ON t.id = utype.ticket_id AND utype.field_id =3
LEFT JOIN j25_support_field_value AS cust ON t.id = cust.ticket_id AND cust.field_id =4
LEFT JOIN j25_support_field_value AS refno ON t.id = refno.ticket_id AND refno.field_id =5

Answer
ALTER TABLE j25_support_field_value
ADD INDEX (`ticket_id`,`field_id`,`field_value`(50))

This index will work as a covering index for your query. It will allow the joins to use only this index to look up the values. It should perform massively faster than without this index, since currently your query would have to read every row in the table to find what matches each combination of ticket_id and field_id.

I would also suggest converting your tables to InnoDB engine, unless you have a very explicit reason for using MyISAM.

ALTER TABLE tablename ENGINE=InnoDB
Comments