Slows Querys

Get help from other users here.

Moderators: Developer, Contributor

Post Reply
dangal
Posts: 4
Joined: 08 Oct 2022, 23:08

Slows Querys

Post by dangal »

good afternoon, I am writing to make you a query to see if you had any problems with these selects.
All of them are taking me approximately 7 seconds to run which is generating a slowness in the users of the mantis.
The queries are as follows:

SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) )


SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) ) ORDER BY mantis_bug_table.date_submitted DESC, mantis_bug_table.last_updated DESC LIMIT ? OFFSET ?


SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) ) ORDER BY mantis_bug_table.sticky DESC, mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT ? OFFSET ?


SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_bug_text_table, mantis_project_table, mantis_bug_table LEFT JOIN mantis_bugnote_table ON mantis_bug_table.id = mantis_bugnote_table.bug_id LEFT JOIN mantis_bugnote_text_table ON mantis_bugnote_table.bugnote_text_id = mantis_bugnote_text_table.id WHERE mantis_project_table.enabled = $1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( ( mantis_bug_table.project_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ) AND ( ( mantis_bug_table.view_state = ? ) OR ( mantis_bug_table.reporter_id = ? ) ) ) AND mantis_bug_table.bug_text_id = mantis_bug_text_table.id AND ( ( (summary ILIKE $2) OR (mantis_bug_text_table.description ILIKE $3) OR (mantis_bug_text_table.steps_to_reproduce ILIKE $4) OR (mantis_bug_text_table.additional_information ILIKE $5) OR (mantis_bugnote_text_table.note ILIKE $6) ) ) ORDER BY mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC LIMIT ? OFFSET ?

attach explain plain

thanks a lot!!!
Attachments
2022-10-11_13-20-37.jpg
2022-10-11_13-20-37.jpg (394.79 KiB) Viewed 1103 times
2022-10-11_13-16-47.jpg
2022-10-11_13-16-47.jpg (459.57 KiB) Viewed 1103 times
cas
Posts: 1586
Joined: 11 Mar 2006, 16:08
Contact:

Re: Slows Querys

Post by cas »

Since you use ILIKE in your query, I assume you are using postgres. Does not matter too much but depending on the size of your tables, using this operator might be the cause of the slowness. Sometimes splitting it in multiple separate queries can proof to be faster. In this case by executing the ILIKE's on the smallest result set.

Please do specify your system parameters(OS/PHP/DB/Versions) which can help to give advice
dangal
Posts: 4
Joined: 08 Oct 2022, 23:08

Re: Slows Querys

Post by dangal »

hello cas, thank you very much for the answer, I tell you is a super old mantis, version 1 that is with a postgres also old, version 9.4, the company in which I am working uses the mantis a lot and is afraid to migrate, these queries are generated by the same mantis and are taking a long time which makes running the refresh very slow
cas
Posts: 1586
Joined: 11 Mar 2006, 16:08
Contact:

Re: Slows Querys

Post by cas »

Hi Dangal,
do you know if there has been done customizing on the mantis environment?
If not, you should upgrade (also DB performance has been optimized over the years). You can do a test upgrade and see if all is fine before migrating production.
Which versions are you running of OS,PHP & Mantis?

You can decrease the number of automatic refreshes with some parameters as described earlier.
Post Reply