CODEWITHSUNDEEP
mysqldatabase-performance
Abel
Abel

Reputation: 538

Mysql Database performance and memory consumption

Good Day guys, I need to improve this query:

SELECT `glpi_tickets`.`id`,`glpi_tickets`.`date`,
        `glpi_tickets`.`date_mod`,`glpi_tickets`.`status`, 
        `glpi_tickets`.`users_id_recipient`,`glpi_tickets`.`slas_id`, 
        `glpi_slalevels`.`execution_time`,`glpi_tickets`.`due_date`,
        `glpi_tickets`.`name`,`glpi_tickets`.`content`
    FROM `glpi_tickets` 
    JOIN `glpi_slas` on (`glpi_slas`.`id` = `glpi_tickets`.`slas_id`)
    JOIN `glpi_slalevels` on (`glpi_slas`.`id` = `glpi_slalevels`.`slas_id`) 
    LEFT JOIN `mail_sent` ON (`mail_sent`.`ticket_id` = `glpi_tickets`.`id`)
    WHERE  (`glpi_tickets`.`closedate` IS NULL 
        OR `glpi_tickets`.`solvedate` IS NULL ) 
        AND `glpi_tickets`.`is_deleted` = 0 
        AND `mail_sent`.`ticket_id` IS NULL

The problem is that when the DB is big as in a lot of rows then it takes to much memory. Basically i have this tables: glpi_tickets with some fields as you can see on the query. I have glpi_slas and glpi_slalevels, from those two the only field that I am really interested in is glpi_slalevels.execution_time, then glpi_tickets is related to glpi_slalevels through glpi_slas. I need to find if this and know if it has been sent an e-mail already from mail_sent.ticket_id. That is it. But I can't get it done using a memory economical way. As an additional detail once that mail_sent.ticket_id is already in the table mail_sent, I do not even need to use/read it any more... I can easily forget about it's existence it's a one time task. Any Idea? Thank you

Edit: Is there a way that I can spit this query in two or three to get the same result in a less memory consuming solution with the same result?

Upvotes: 0

Views: 91

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Using an outer join to check for non-existence is a trick that can result in huge intermediate results. An example: 100000 records in t, 99999 of them with 100 mail_sent each and one without any mail_sent. Your intermediate result contains 9999901 joined records then, which you scan to find the one record where mail_sent.ticket_id is null. A dbms may find a way to handle this effectively or not. Have you tried the straight-forward way already? You want to now if records exist, so you'd use the EXISTS clause.

I suppose a full table scan will be used on glpi_tickets, as OR conditions are not that easy to handle quickly with an index and it's hard for the dbms to estimate how many records may be affected. If, however, there are many records in glpi_tickets, but only a tiny percentage of unclosed unsolved undeleted records, you'll want an index to be used. The index needed would be (is_deleted, closedate, solvedate) or (is_deleted, solvedate, closedate). (Unfortunately there are no function indexes in MySQL which would make a very small index possible here.) You can even force its usage with USE INDEX.

(I take it for granted that your tables are properly created with primary and foreign keys, so you have indexes for the joins.)

select 
  t.id,
  t.date,
  t.date_mod,
  t.status,
  t.users_id_recipient,
  t.slas_id,
  sl.execution_time,
  t.due_date,
  t.name,
  t.content
from glpi_tickets t 
join glpi_slas s on s.id = t.slas_id
join glpi_slalevels sl on s.id = sl.slas_id
where (t.closedate is null or t.solvedate is null) 
and t.is_deleted = 0 
and not exists
(
  select *
  from mail_sent
  where mail_sent.ticket_id = t.id
);

If an index helps, but you need this still faster, then you can create an additional column is_open which you compute in before-insert / before-update triggers. E.g.:

CREATE TRIGGER trg_insert_glpi_tickets BEFORE INSERT ON glpi_tickets 
  FOR EACH ROW BEGIN
    SET NEW.is_open = (NEW.closedate is null or NEW.solvedate is null) and NEW.is_deleted = 0;
  END;

Then create an index on the field and re-write your query to where is_open = 1. This would be a very small, efficient index and access should be extremely fast.

Upvotes: 1

Related Questions