Professional Web Applications Themes

Weird problem querying mysql - MySQL

I have a query that I have written which really bothers me. There are two tables involved. table login lo_id bigint PK table member mem_id bigint PK lo_id bigint FK Any query I run on either of these tables returns almost immediately. But this fat query runs forever and I don't think returns anything although it should. select count(*) from login where lo_id in ( select a.lo_id from member a left join login b on a.lo_id = b.lo_id where a.lo_id is not null ) The subquery returns fine. A Select count(*) from login return fines. The funny thing is that ...

  1. #1

    Default Weird problem querying mysql

    I have a query that I have written which really bothers me.

    There are two tables involved.

    table login
    lo_id bigint PK


    table member
    mem_id bigint PK
    lo_id bigint FK

    Any query I run on either of these tables returns almost immediately.

    But this fat query runs forever and I don't think returns anything
    although it should.

    select count(*)
    from login
    where lo_id in (
    select a.lo_id
    from member a
    left join login b on a.lo_id = b.lo_id
    where a.lo_id is not null
    )

    The subquery returns fine.

    A

    Select count(*) from login

    return fines.

    The funny thing is that although this subquery:

    select a.lo_id
    from member a
    left join login b on a.lo_id = b.lo_id
    where a.lo_id is not null

    returns fine. The following alternative query although the result would
    be different does not return:

    select a.lo_id
    from login a
    left join member b on a.lo_id = b.lo_id
    where a.lo_id is not null

    Anyone have a clue as to what is happening.

    Thanks
    Don Vaillancourt Guest

  2. #2

    Default Re: Weird problem querying mysql

    Well I added LIMIT 1 to my query and it returned in 6 seconds. I don't
    know why so much. So perhaps it's an index issue. But if I run the
    following query:

    select count(*) from login where lo_id in (1001, 1002,1003, 1004)

    it returns right away.

    This is way too weird.


    Don Vaillancourt wrote:
    > I have a query that I have written which really bothers me.
    >
    > There are two tables involved.
    >
    > table login
    > lo_id bigint PK
    >
    >
    > table member
    > mem_id bigint PK
    > lo_id bigint FK
    >
    > Any query I run on either of these tables returns almost immediately.
    >
    > But this fat query runs forever and I don't think returns anything
    > although it should.
    >
    > select count(*)
    > from login
    > where lo_id in (
    > select a.lo_id
    > from member a
    > left join login b on a.lo_id = b.lo_id
    > where a.lo_id is not null
    > )
    >
    > The subquery returns fine.
    >
    > A
    >
    > Select count(*) from login
    >
    > return fines.
    >
    > The funny thing is that although this subquery:
    >
    > select a.lo_id
    > from member a
    > left join login b on a.lo_id = b.lo_id
    > where a.lo_id is not null
    >
    > returns fine. The following alternative query although the result would
    > be different does not return:
    >
    > select a.lo_id
    > from login a
    > left join member b on a.lo_id = b.lo_id
    > where a.lo_id is not null
    >
    > Anyone have a clue as to what is happening.
    >
    > Thanks
    Don Vaillancourt Guest

  3. #3

    Default Re: Weird problem querying mysql

    "Don Vaillancourt" <donvwebimpact.com> wrote in message
    news:CFsBf.9954$43.6206nnrp.ca.mci.com!nnrp1.uune t.ca...
    > The funny thing is that although this subquery:
    >
    > select a.lo_id
    > from member a
    > left join login b on a.lo_id = b.lo_id
    > where a.lo_id is not null
    >
    > returns fine. The following alternative query although the result would
    > be different does not return:
    Does not return _ever_? Or just takes a long time?
    > select a.lo_id
    > from login a
    > left join member b on a.lo_id = b.lo_id
    > where a.lo_id is not null
    >
    > Anyone have a clue as to what is happening.
    Have you tried using EXPLAIN? You can use this to find out if both queries
    are using indexes in the same ways.
    [url]http://dev.mysql.com/doc/refman/5.0/en/explain.html[/url]

    I'd also recommend making sure you have indexes defined on the relevant
    lo_id columns in both tables. And that these indexes have been rebuilt
    recently. See docs on YZE TABLE and OPTIMIZE TABLE.
    [url]http://dev.mysql.com/doc/refman/5.0/en/yze-table.html[/url]
    [url]http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html[/url]

    I'm also noticing that you're using "is not null" with outer joins, but
    you're testing for nulls in "a" which is on the left side of the left outer
    join. I think you have that reversed; the _right_ side of a left outer
    join is the one that can have nulls where there are no matching rows.
    Checking "a.lo_id is not null" where a.lo_id is a primary key field is a
    no-op.

    I don't know that the latter has anything to do with your performance issue,
    but it's a potential mistake in your query.

    Regards,
    Bill K.


    Bill Karwin Guest

  4. #4

    Default Re: Weird problem querying mysql

    A limit of 6 return the result in 6 seconds. So it is working. But
    when I increased the limit to 12 it returned in 50 seconds. So the time
    is exponential.

    I did have a look at Explain and only one index was being used and in
    some cases it seemed like a table scan was required.

    I'll have a look at the links you provided.

    Thanks


    Bill Karwin wrote:
    > "Don Vaillancourt" <donvwebimpact.com> wrote in message
    > news:CFsBf.9954$43.6206nnrp.ca.mci.com!nnrp1.uune t.ca...
    >> The funny thing is that although this subquery:
    >>
    >> select a.lo_id
    >> from member a
    >> left join login b on a.lo_id = b.lo_id
    >> where a.lo_id is not null
    >>
    >> returns fine. The following alternative query although the result would
    >> be different does not return:
    >
    > Does not return _ever_? Or just takes a long time?
    >
    >> select a.lo_id
    >> from login a
    >> left join member b on a.lo_id = b.lo_id
    >> where a.lo_id is not null
    >>
    >> Anyone have a clue as to what is happening.
    >
    > Have you tried using EXPLAIN? You can use this to find out if both queries
    > are using indexes in the same ways.
    > [url]http://dev.mysql.com/doc/refman/5.0/en/explain.html[/url]
    >
    > I'd also recommend making sure you have indexes defined on the relevant
    > lo_id columns in both tables. And that these indexes have been rebuilt
    > recently. See docs on YZE TABLE and OPTIMIZE TABLE.
    > [url]http://dev.mysql.com/doc/refman/5.0/en/yze-table.html[/url]
    > [url]http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html[/url]
    >
    > I'm also noticing that you're using "is not null" with outer joins, but
    > you're testing for nulls in "a" which is on the left side of the left outer
    > join. I think you have that reversed; the _right_ side of a left outer
    > join is the one that can have nulls where there are no matching rows.
    > Checking "a.lo_id is not null" where a.lo_id is a primary key field is a
    > no-op.
    >
    > I don't know that the latter has anything to do with your performance issue,
    > but it's a potential mistake in your query.
    >
    > Regards,
    > Bill K.
    >
    >
    Don Vaillancourt Guest

  5. #5

    Default Re: Weird problem querying mysql


    "Don Vaillancourt" <donvwebimpact.com> wrote in message
    news:O6vBf.9968$43.5973nnrp.ca.mci.com!nnrp1.uune t.ca...
    >A limit of 6 return the result in 6 seconds. So it is working. But when I
    >increased the limit to 12 it returned in 50 seconds. So the time is
    >exponential.
    >
    > I did have a look at Explain and only one index was being used and in some
    > cases it seemed like a table scan was required.
    If you can avoid table scans, do it. Put an INDEX on something. optimize
    your query. You probably already know this, but this is where an entire
    table is scanned row by row. In your case, I hope it's not being done
    repeatedly.
    I am told primary keys automatically have a UNIQUE INDEX on them.
    >
    > I'll have a look at the links you provided.
    >
    > Thanks
    >
    >
    > Bill Karwin wrote:
    >> "Don Vaillancourt" <donvwebimpact.com> wrote in message
    >> news:CFsBf.9954$43.6206nnrp.ca.mci.com!nnrp1.uune t.ca...
    >>> The funny thing is that although this subquery:
    >>>
    >>> select a.lo_id
    >>> from member a
    >>> left join login b on a.lo_id = b.lo_id
    >>> where a.lo_id is not null
    >>>
    >>> returns fine. The following alternative query although the result would
    >>> be different does not return:
    >>
    >> Does not return _ever_? Or just takes a long time?
    >>
    >>> select a.lo_id
    >>> from login a
    >>> left join member b on a.lo_id = b.lo_id
    >>> where a.lo_id is not null
    >>>
    >>> Anyone have a clue as to what is happening.
    >>
    >> Have you tried using EXPLAIN? You can use this to find out if both
    >> queries are using indexes in the same ways.
    >> [url]http://dev.mysql.com/doc/refman/5.0/en/explain.html[/url]
    >>
    >> I'd also recommend making sure you have indexes defined on the relevant
    >> lo_id columns in both tables. And that these indexes have been rebuilt
    >> recently. See docs on YZE TABLE and OPTIMIZE TABLE.
    >> [url]http://dev.mysql.com/doc/refman/5.0/en/yze-table.html[/url]
    >> [url]http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html[/url]
    >>
    >> I'm also noticing that you're using "is not null" with outer joins, but
    >> you're testing for nulls in "a" which is on the left side of the left
    >> outer join. I think you have that reversed; the _right_ side of a left
    >> outer join is the one that can have nulls where there are no matching
    >> rows. Checking "a.lo_id is not null" where a.lo_id is a primary key field
    >> is a no-op.
    >>
    >> I don't know that the latter has anything to do with your performance
    >> issue, but it's a potential mistake in your query.
    >>
    >> Regards,
    >> Bill K.

    Jim Michaels Guest

Similar Threads

  1. Problem querying Access DB for records within a certaindate range.
    By liquidorb in forum Coldfusion Database Access
    Replies: 5
    Last Post: January 19th, 03:25 AM
  2. Querying a Query problem
    By cmreis in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 1st, 06:54 PM
  3. problem with querying search results
    By choirellie in forum Macromedia ColdFusion
    Replies: 1
    Last Post: March 24th, 02:02 AM
  4. Problem querying LDAP and/or Active Directory
    By Andrew in forum ASP.NET Security
    Replies: 1
    Last Post: June 24th, 01:08 PM
  5. Replies: 0
    Last Post: August 23rd, 11:56 AM

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