OR statement crashes MySQL

[u3003-04-23]

I am running MySQL 3.23.55 on Windows 2000, using IIS and ASP.

Am also running SQLyog v3.1 as the frontend (if it matters).

The SQL below for some reason is crashing mySQL. When I execute it, from either within SQLyog or within the ASP page, mysqld-nt.exe when I look in Task Manager hogs approx 90% system resources and I have to go into Services to stop the MySQL service to get the computer running again.

This is the SQL I am running.

No doubt is is stunningly ignorant, and rubbish.

However, it used to work in MS Access – I am currently converting my Access data to MySQL.

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_teacher_main.InstrumentKey= 6)

OR

(v_teacher_main.InstrumentKey= 1)

The reason I have the OR bit in (which is definitely causing the crash – it works if I take out the OR bit and all below it) is that I have to search the database for teachers whose instrumentKey is 6 (violin) OR 1 (viola).

I have checked the MySQL manual, and found nothing glaringly obvious about what I'm doing wrong here.

Thanks

Jim

[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