Mysql infinite loop?!

this query doesnt work:
SELECT d.cID,d.cLName,d.cFName , k.kid
FROM cname d, cu_key e, rep_key f , `key` k
WHERE (d.cLName LIKE '%') and (((e.cID = d.cID) and
(k.kID=e.keyID))or((f.repID = d.cID)
and (k.kID=f.keyID))) and
( (k.UserSupportExp >= '2001-01-01' ) and (k.UserSupportExp
<='2002-01-01') )
order by d.cLName,d.cFName LIMIT 0, 50

if i take out either of the statments connected by the or clause, the
query works, otherwise it times out after 3000 seconds.

SELECT d.cID,d.cLName,d.cFName , k.kid
FROM cname d, cu_key e, rep_key f , `key` k
WHERE (d.cLName LIKE '%') and (((e.cID = d.cID) and (k.kID=e.keyID)))
and
( (k.UserSupportExp >= '2001-01-01' ) and (k.UserSupportExp
<='2002-01-01') )
order by d.cLName,d.cFName LIMIT 0, 50

any ideals?
thanks in advance nick

StockN@gmail.com wrote:
> SELECT d.cID,d.cLName,d.cFName , k.kid
> FROM cname d, cu_key e, rep_key f , `key` k
> WHERE (d.cLName LIKE '%') and (((e.cID = d.cID) and
> (k.kID=e.keyID))or((f.repID = d.cID)
> and (k.kID=f.keyID))) and
> ( (k.UserSupportExp >= '2001-01-01' ) and (k.UserSupportExp
> <='2002-01-01') )
> order by d.cLName,d.cFName LIMIT 0, 50
>
> if i take out either of the statments connected by the or clause, the
> query works, otherwise it times out after 3000 seconds.

So it takes a long time, but this doesn't mean it's an infinite loop.
It just means your query is very expensive and takes too long.

What is the purpose of using "LIKE '%'"? This use of the wildcard
matches all rows, but ensures no index can be used, so it forces a
table-scan on your cname table. This can be costly, and probably causes
the query to take a long time, thus you're getting a timeout.

This might not be the only performance problem. What indexes do you
have in these tables? Have you used EXPLAIN to analyze the query, so
you know what additional indexes you should create?

You also have a non-normalized relationship between your tables: k.kID
and d.cID can reference either of two tables (cu_key or rep_key). I
predict that this design will become harder and harder to manage, until
it becomes completely broken. You should combine the cu_key and rep_key
tables into one table, so you can have a cleaner reference relationship
between your tables.

Regards,
Bill K.