Query Stalls when Criteria is changed

[Phil Smith]

I have a relatively complex query, using about 12 tables.

One field is BRAND.

If I choose one brand, say "teflon", which represents about 5% of the
records I am looking for, It runs in about 2 seconds.

If, however, I change that criteria to be <>"teflon", it does not
finish. Access stops refreshing the screen, looking at the task manager
shows zero CPU activity, looking at process list for MySQL sows no
connections, it just sits there until I break out of access. No other
change is made to the query. If I remove the criteria for that field
completely, it hangs.

More weirdness?

If I choose "teflon" for the criteria, it takes about 2 seconds to
return 114 records.

Choose "krylon", it takes about a second to return 5 records.
Choose "teflon" or "krylon", it takes abut 5 minutes to return.

What the heck?

Phil

 

[vanderghast]

I don't know MySQL, but is it possible that its execution plan get totally
changed when using <> instead of = ? Is it possible to FORCE MySQL to first
look for records <> 'teflon' and only after, to make the join(s) implying
that table? With Jet, you can do it by first writing a query that returns
that filetered table:

SELECT * FROM mytable WHERE brand <>'teflon'

and next, use that query (instead of the table) in your main query.


Vanderghast, Access MVP