Query hanging 1

[Wullie]

Hi,

I've just migrated a site between servers and now I'm getting a problem where the following query is just hanging.

SELECT *
FROM jobs AS Main
WHERE id = (SELECT MAX(Sub.ID) FROM jobs AS Sub WHERE Sub.multiplejob = Main.multiplejob)
AND      daysuntilflag <= -1
AND      currentstatus != 'JobComplete'
AND      currentstatus != 'JobCancelled'
AND      jobnumber NOT LIKE '%cancelled%'
ORDER BY daysuntilflag, id asc

The server that runs the query successfully runs 5.0.77 and the server that doesn't runs 5.1.48

The table has about 4200 records in it.

Does anyone have any suggestions for what could possibly cause this to hang?

Thanks in advance,

Wullie

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[feherke]

Hi

No idea what that migration means, but first I would check if indexes still exist.

Then I would try to move the sub-[tt]select[/tt] from the [tt]where[/tt] clause to the [tt]from[/tt] clause :

[b]select[/b]
main[teal].*[/teal]

[b]from[/b] jobs [b]as[/b] main
[b]inner[/b] [b]join[/b] [teal]([/teal]
  [b]select[/b]
  max[teal]([/teal]id[teal])[/teal] [b]as[/b] id

  [b]from[/b] jobs

  [b]group[/b] [b]by[/b] multiplejob
[teal])[/teal] [b]as[/b] sub [b]using[/b] [teal]([/teal]id[teal])[/teal]

[b]where[/b] daysuntilflag [teal]<=[/teal] [teal]-[/teal][purple]1[/purple]
[b]and[/b] currentstatus [teal]!=[/teal] [green][i]'JobComplete'[/i][/green]
[b]and[/b] currentstatus [teal]!=[/teal] [green][i]'JobCancelled'[/i][/green]
[b]and[/b] jobnumber [b]not[/b] [b]like[/b] [green][i]'%cancelled%'[/i][/green]

[b]order[/b] [b]by[/b] daysuntilflag[teal],[/teal] id [b]asc[/b]

[small][maroon]Warning[/maroon] The above code was not tested[/small]


Feherke.

 

[Wullie]

Hi mate,

Thanks, your query worked and is much quicker to run than the old one.

Just curious though why the old code would have worked on the older mysql version but not the new one, anyone have any ideas?

Thanks again, much appreciated.

Wullie

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[feherke]

Hi

I see no expert answers this, so I take the chance to mention my pale idea : maybe an [tt]analyze table[/tt] on the jobs table was missing.

As far as I remember, there could be significant difference on [tt]count(*)[/tt] when the database's internal statistic on the table is not up to date. But I have no idea if applies to the current problem too.

Feherke.