Professional Web Applications Themes

Mysql infinite loop?! - MySQL

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' ...

  1. #1

    Default 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 Guest

  2. #2

    Default Re: Mysql infinite loop?!

    [email]StockN[/email] 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 yze 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.
    Bill Karwin Guest

Similar Threads

  1. Infinite loop mounting disk
    By Michael in forum Ubuntu
    Replies: 1
    Last Post: May 14th, 11:16 PM
  2. Weird Infinite loop
    By Andrew Milne in forum PHP Development
    Replies: 4
    Last Post: September 29th, 12:43 PM
  3. #25037 [Opn->Asn]: SendText infinite loop
    By iliaa@php.net in forum PHP Development
    Replies: 0
    Last Post: August 11th, 01:58 PM
  4. #25037 [Opn]: SendText infinite loop
    By richard at bradders2000 dot co dot uk in forum PHP Development
    Replies: 0
    Last Post: August 11th, 11:01 AM
  5. How do i stop an infinite loop in MX director?
    By David Downie in forum Macromedia Director Lingo
    Replies: 1
    Last Post: July 27th, 06:13 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139