Mysql Database performance and memory consumption

Question

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?

Since you are left joining abel_mail_sent and you don't use it at all, you might as well remove it. The only effect it has now, is duplicating rows if you have more than one mail for a ticket. -edit- Oh wait, I see you are referring to mail_sent in the where clause. That seems to be an error, since mail_sent is not an alias. This query would fail. Anyway, you might try to change it to a 'not exist` to check if it doesn't have mail. — GolezTrol
– GolezTrol, Commented Dec 14, 2014 at 10:33
is this feild "glpi_tickets.slas_id" indexed ? could you try adding an index to that ? also the field abel_mail_sent.ticket_id — Althaf M
– Althaf M, Commented Dec 14, 2014 at 10:35
ok thank you I will try, but I am not sure it will be enough — Abel
– Abel, Commented Dec 14, 2014 at 10:37
@GolezTrol it's true it's an error, I did nit put the right name on the query. I'll edit it now.. please check. I made a mistake rewriting the table names trying to make it easy to understand. mail_sent.ticket_id it's actually important. — Abel
– Abel, Commented Dec 14, 2014 at 19:16

Thorsten Kettner · Accepted Answer · 2014-12-15 08:35:51Z

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.

Collectives™ on Stack Overflow

Mysql Database performance and memory consumption

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related