MySQL is slow when it comes to such JOINs. postgres would still cope with it, but when things become more complicated you'll have to switch to Microsoft SQL Server or Oracle.
Some things you can try:
- set an index on test2.type
- set an index on test2.SDURN
- set an index on viewm16journeysq.`SDU Reference No`
- remove the cast
- try a WHERE clause:
...left join test2 t2 on v.`SDU Reference No` = t2.SDURN
WHERE t2.type not in (1,2,3,4)
- which values can test2.type have? Can you change the clause to test2.type>4?
- is viewm16journeysq a table or a view? In the latter case, change your query such that it uses the underlying tables instead of the view.