OR statement crashes MySQL

[Shadow]

There could be a problem with the parenthesis you use because AND has a precedence over OR, so the last portion of your query should be modified as:

WHERE v_all.pid = v_teacher_main.pid

AND

v_teacher_main.instrumentkey = v_def_instruments.instrumentkey

AND

v_all.pCounty = v_county.countyid

AND

(v_teacher_main.InstrumentKey= 6

OR

v_teacher_main.InstrumentKey= 1)

Your version is likely to end up with a lot larger result set than the above one and if the tables concerned in this query are large, the resultset may grow to a size that cannot be handled by MySql perfectly.

[nero]

Well that wil make a recordset result of

count(v_all) * count(v_teacher_main) * count(v_def_instruments) * count(v_county)

when I am right and that can be the problem.

Try to modify your query as follows. That can be the proper resultset you want.

SELECT v_all.*, v_teacher_main.*, v_def_instruments.*, v_county.*

FROM v_all

JOIN v_teacher_main

ON v_all.pid = v_teacher_main.pid AND (v_teacher_main.InstrumentKey= 6 OR v_teacher_main.InstrumentKey= 1)

JOIN v_def_instruments

ON v_teacher_main.instrumentkey = v_def_instruments.instrumentkey

JOIN v_county

ON v_all.pCounty = v_county.countyid

Maybe that will do the job.

nero :ph34r:

[u3003-04-23]

Hi Nero,

thanks for the help.

It turned out to be due to the parentheses being in the wrong place.

This works now:

SELECT v_all.*, v_teacher_main.*, v_def_instruments.*, v_county.*

FROM v_all, v_teacher_main, v_def_instruments, v_county

WHERE v_all.pid = v_teacher_main.pid

AND v_teacher_main.instrumentkey = v_def_instruments.instrumentkey

AND v_all.pCounty = v_county.countyid

AND v_all.pCounty = 133

AND ((v_teacher_main.InstrumentKey= 6) OR (v_all.pCounty = 133 AND v_teacher_main.InstrumentKey= 1))

ORDER BY pName, countyName limit 0,25

Thanks

Jim

—

http://www.jimpix.co.uk

[Author]

Posts