Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts

    MySQL Query Timeout Problem

    Are there any SQL gurus out there who could take a look at the following code and spot any errors or better methods? The query below is being run on a table with about 300K records and it's timing out at 6000 seconds.

    [code]

    SELECT T1.*

    FROM mytable AS T1,

    (SELECT col1, col2, COUNT (col1) AS cnt
    FROM mytable
    GROUP BY col1
    HAVING cnt>1
    ) AS T2

    WHERE T1.col1 = T2.col1
    AND T1.col2 = T2.col2
    ORDER BY T1.col1

    [code]

    Any assistance or suggestions would be greatly appreciated.

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,530
    Thanks
    45
    Thanked 259 Times in 256 Posts
    What is its purpose? It seems like you're trying to find out which rows have an aggregate col1 count greater then 1? Is there a reason you can't use limit? I'm sure OP will know better then me, but on the surface, nothing looks wrong/bad here; seems more like a server issue or malformed table structure. You have a solid primary key? Good normalization?

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    One fatal flaw in there is the GROUP BY: It is *ONLY* grouping on COL1.

    Any DB except MySQL would not even allow that. And even with MySQL that gives oddball results.

    For example, if you had these table contents:
    Code:
    COL1 -- COL2
        1 -- 1
        1 -- 2
        1 -- 3
        2 -- 1
    and then you do
    Code:
    SELECT COL1, COL2, COUNT(COL1) 
    FROM table GROUP BY COL1
    That will give you OUTPUT of
    Code:
    COL1 -- COL2 -- count
        1 -- 1    -- 3
        2 -- 1    -- 1
    Except that there is no predicting what value you will get for that red 1. It could be 1 or 2 or 3.

    And now, when you go to JOIN that sub-select, you have NO IDEA which value of COL2 you will be joining on!

    ******
    Keleth: Your instincts are right. But LIMIT doesn't help here. That would limit him to a total of N records, not N per COL1 value.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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