Sfrgttk

Are babies of evil humanoid species inherently evil?

Do I really need to have a scientific explanation for my premise?

What to do when during a meeting client people start to fight (even physically) with each others?

PTIJ: Why can't I eat anything?

How to pass a string to a command that expects a file?

Word for a person who has no opinion about whether god exists

Is it true that real estate prices mainly go up?

How strictly should I take "Candidates must be local"?

My story is written in English, but is set in my home country. What language should I use for the dialogue?

How do I locate a classical quotation?

How do you like my writing?

What is the chance of making a successful appeal to dismissal decision from a PhD program after failing the qualifying exam in the 2nd attempt?

Things to avoid when using voltage regulators?

What wound would be of little consequence to a biped but terrible for a quadruped?

Make a transparent 448*448 image

Why would a jet engine that runs at temps excess of 2000°C burn when it crashes?

Could you please stop shuffling the deck and play already?

Grey hair or white hair

Does "variables should live in the smallest scope as possible" include the case "variables should not exist if possible"?

Why is Beresheet doing a only a one-way trip?

A question on the ultrafilter number

In the late 1940’s to early 1950’s what technology was available that could melt a LOT of ice?

MTG: Can I kill an opponent in response to lethal activated abilities, and not take the damage?

Rejected in 4th interview round citing insufficient years of experience

Optimise multiple inner JOIN

mySQL efficiency with inner join queriesHow to optimise multiple joins with OR?Subquery inside a select vs left joinOptimise a LATERAL JOIN query on a big tableHow to optimise this query?Simple Inner join suggesting a Include indexOptimise Large Record Deletion ProcedureCardinality estimation problem on inner joinwhy is update from a join containing zero records taking a minute and 20 seconds?schedule join multiple tables

0

Consider the following query:

 mysql> SELECT GROUP_CONCAT(a.id SEPARATOR 'n')

    -> FROM assignment a

    -> INNER JOIN statements s ON a.id = s.assignment_id

    -> INNER JOIN assignment_status_log asl ON a.status_log_id = asl.id

    -> INNER JOIN assignment_status ags ON asl.status_id = ags.id

    -> WHERE ags.category <> 'PASSED'

    -> AND ags.category <> 'TEST'

    -> AND ags.category <> 'REJECTED'

    -> AND ags.category <> 'CANCELED'

    -> AND ags.category <> 'NA'

    -> AND s.id IS NOT NULL

    -> AND s.id IN (SELECT MAX(sts.id) AS dctrn__1 FROM assignment agr INNER JOIN statements sts ON agr.id = sts.assignment_id WHERE agr.id = a.id)

    -> AND s.grade > 0

    -> AND ags.types <> 'CB'

    -> AND ags.types <> 'CD'

    -> AND ags.types <> 'CE'

    -> AND ags.types <> 'CF'

    -> GROUP BY a.id;

I would like to have an idea as to how I can troubleshoot what is taking so long. I have added index on s.grades and ags.types column, and am pondering if I would need to try to have the conditions on the JOIN instead of in WHERE clause. It has currently been running for close to 2 hours, on 500 000 record. Awful.

Any insight or hints as to what is affecting the performance on this will be greatly appreciated.

query-performance optimization mysql-5.7

share

asked 4 mins ago

stefgosselinstefgosselin

1104

add a comment | 

0

Consider the following query:

 mysql> SELECT GROUP_CONCAT(a.id SEPARATOR 'n')

    -> FROM assignment a

    -> INNER JOIN statements s ON a.id = s.assignment_id

    -> INNER JOIN assignment_status_log asl ON a.status_log_id = asl.id

    -> INNER JOIN assignment_status ags ON asl.status_id = ags.id

    -> WHERE ags.category <> 'PASSED'

    -> AND ags.category <> 'TEST'

    -> AND ags.category <> 'REJECTED'

    -> AND ags.category <> 'CANCELED'

    -> AND ags.category <> 'NA'

    -> AND s.id IS NOT NULL

    -> AND s.id IN (SELECT MAX(sts.id) AS dctrn__1 FROM assignment agr INNER JOIN statements sts ON agr.id = sts.assignment_id WHERE agr.id = a.id)

    -> AND s.grade > 0

    -> AND ags.types <> 'CB'

    -> AND ags.types <> 'CD'

    -> AND ags.types <> 'CE'

    -> AND ags.types <> 'CF'

    -> GROUP BY a.id;

I would like to have an idea as to how I can troubleshoot what is taking so long. I have added index on s.grades and ags.types column, and am pondering if I would need to try to have the conditions on the JOIN instead of in WHERE clause. It has currently been running for close to 2 hours, on 500 000 record. Awful.

Any insight or hints as to what is affecting the performance on this will be greatly appreciated.

query-performance optimization mysql-5.7

share

asked 4 mins ago

stefgosselinstefgosselin

1104

add a comment | 

Consider the following query:

 mysql> SELECT GROUP_CONCAT(a.id SEPARATOR 'n')

    -> FROM assignment a

    -> INNER JOIN statements s ON a.id = s.assignment_id

    -> INNER JOIN assignment_status_log asl ON a.status_log_id = asl.id

    -> INNER JOIN assignment_status ags ON asl.status_id = ags.id

    -> WHERE ags.category <> 'PASSED'

    -> AND ags.category <> 'TEST'

    -> AND ags.category <> 'REJECTED'

    -> AND ags.category <> 'CANCELED'

    -> AND ags.category <> 'NA'

    -> AND s.id IS NOT NULL

    -> AND s.id IN (SELECT MAX(sts.id) AS dctrn__1 FROM assignment agr INNER JOIN statements sts ON agr.id = sts.assignment_id WHERE agr.id = a.id)

    -> AND s.grade > 0

    -> AND ags.types <> 'CB'

    -> AND ags.types <> 'CD'

    -> AND ags.types <> 'CE'

    -> AND ags.types <> 'CF'

    -> GROUP BY a.id;

I would like to have an idea as to how I can troubleshoot what is taking so long. I have added index on s.grades and ags.types column, and am pondering if I would need to try to have the conditions on the JOIN instead of in WHERE clause. It has currently been running for close to 2 hours, on 500 000 record. Awful.

Any insight or hints as to what is affecting the performance on this will be greatly appreciated.

query-performance optimization mysql-5.7

share

asked 4 mins ago

stefgosselinstefgosselin

1104

 mysql> SELECT GROUP_CONCAT(a.id SEPARATOR 'n')

    -> FROM assignment a

    -> INNER JOIN statements s ON a.id = s.assignment_id

    -> INNER JOIN assignment_status_log asl ON a.status_log_id = asl.id

    -> INNER JOIN assignment_status ags ON asl.status_id = ags.id

    -> WHERE ags.category <> 'PASSED'

    -> AND ags.category <> 'TEST'

    -> AND ags.category <> 'REJECTED'

    -> AND ags.category <> 'CANCELED'

    -> AND ags.category <> 'NA'

    -> AND s.id IS NOT NULL

    -> AND s.id IN (SELECT MAX(sts.id) AS dctrn__1 FROM assignment agr INNER JOIN statements sts ON agr.id = sts.assignment_id WHERE agr.id = a.id)

    -> AND s.grade > 0

    -> AND ags.types <> 'CB'

    -> AND ags.types <> 'CD'

    -> AND ags.types <> 'CE'

    -> AND ags.types <> 'CF'

    -> GROUP BY a.id;

share

asked 4 mins ago

stefgosselinstefgosselin

1104

share

asked 4 mins ago

stefgosselinstefgosselin

1104

asked 4 mins ago

stefgosselinstefgosselin

1104

asked 4 mins ago

stefgosselinstefgosselin

1104