[msql-list] Query Hangs

[A. R. M.]

Hi,

I have a page that generates a report for administrative purposes where in
the past I've used only one sort of reporting: Give me everything.
For that report, I had a query that joined two tables and it worked fine
like this:

SELECT jls.emp_name,jls.p_code,jls.emp_code,jls.category,jls.job_title,
employers.prim_contact,employers.phone,employers.email FROM jls,employers
WHERE jls.emp_code=employers.emp_code ORDER BY jls.emp_name,jls.emp_code

Recently, I see a need to distinguish the resulting figures by area. More
specifically, the same numbers generated above, but rather than ALL results,
I'd only need to get the figures for, say, Summer, or Part-time postings.
I have a field called area which has data as such:
'|F' or '|S' or '|F|P|S|V'

Now, when I do the following query, the engine hangs for about a minute and
then exits with an empty error string.

SELECT jls.emp_name,jls.p_code,jls.emp_code,jls.category,jls.job_title,
employers.prim_contact,employers.phone,employers.email FROM jls,employers
WHERE (jls.area CLIKE 'S' AND jls.emp_code=employers.emp_code)
ORDER BY jls.emp_name,jls.emp_code

I tried using regular expression instead of CLIKE with "jls.area RLIKE
'.*S.*'" thinking maybe the pipes in that field cause the error but that
didn't work either.
When I try the query with only "WHERE jls.area CLIKE 'S'" it works. So it
appears that the problem manifests itself when using a table join, in
conjunction with the "AND" in the WHERE clause.

I upgraded to 3.8 yesterday and also used msqlexplain to no avail.

Any ideas please?

Thanks.
Ali Mojahed.

-----------------------------------------------------------------
This is the Mini SQL Mailing List operated by Hughes Technologies
To unsubscribe, go to http://www.Hughes.com.au/extras/email/

[David J. Hughes]

Hi Ali,

Is it possible for you to send me a dump of the database involved?
Also details of the platform involved (os version etc) would be great.
If you can send me the data I'll try to reproduce the problem.

Bambi
..