Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
Thread: MySQL Query Timeout Problem
08-22-2013, 09:41 AM #1
- Join Date
- Jan 2013
- 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.
FROM mytable AS T1,
(SELECT col1, col2, COUNT (col1) AS cnt
GROUP BY col1
) AS T2
WHERE T1.col1 = T2.col1
AND T1.col2 = T2.col2
ORDER BY T1.col1
Any assistance or suggestions would be greatly appreciated.
08-22-2013, 03:14 PM #2
- Join Date
- Jun 2008
- New Jersey
- 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?
08-22-2013, 07:24 PM #3
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:
and then you doCode:COL1 -- COL2 1 -- 1 1 -- 2 1 -- 3 2 -- 1That will give you OUTPUT ofCode:SELECT COL1, COL2, COUNT(COL1) FROM table GROUP BY COL1
Except that there is no predicting what value you will get for that red 1. It could be 1 or 2 or 3.Code:COL1 -- COL2 -- count 1 -- 1 -- 3 2 -- 1 -- 1
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.