Optimizing my sql query

Question

I have created the following query to make a "frequency diagram" that displays the number of rows that are between certain values:

SELECT CONCAT('0 - ', M.MaxField1) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1<M.MaxField1
UNION ALL
SELECT CONCAT((M.MaxField1), ' - ', (M.MaxField1*2)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1>=(M.MaxField1) AND P.Field1<(M.MaxField1*2)
UNION ALL
SELECT CONCAT((M.MaxField1*2), ' - ', (M.MaxField1*3)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1>=(M.MaxField1*2) AND P.Field1<(M.MaxField1*3)
UNION ALL
SELECT CONCAT((M.MaxField1*3), ' - ', (M.MaxField1*4)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1>=(M.MaxField1*3) AND P.Field1<(M.MaxField1*4)
UNION ALL
SELECT CONCAT((M.MaxField1*4), ' - ', (M.MaxField1*5)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1>=(M.MaxField1*4) AND P.Field1<(M.MaxField1*5)
UNION ALL
SELECT CONCAT((M.MaxField1*5), ' - ', (M.MaxField1*6)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1>=(M.MaxField1*5) AND P.Field1<(M.MaxField1*6)
UNION ALL
SELECT CONCAT((M.MaxField1*6), ' - ', (M.MaxField1*7)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1>=(M.MaxField1*6) AND P.Field1<(M.MaxField1*7)
UNION ALL
SELECT CONCAT((M.MaxField1*7), ' - ', (M.MaxField1*8)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1>=(M.MaxField1*7) AND P.Field1<(M.MaxField1*8)
UNION ALL
SELECT CONCAT((M.MaxField1*8), ' - ', (M.MaxField1*9)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1>=(M.MaxField1*8) AND P.Field1<(M.MaxField1*9)
UNION ALL
SELECT CONCAT((M.MaxField1*9), ' - ', (M.MaxField1*10)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M
WHERE P.Field1>=(M.MaxField1*9)

Any suggestions to optimize this script as it is quite time consuming?

Are your histogram buckets evenly spaced and sized?

Pieter Geerkens
– Pieter Geerkens

2013-03-03 14:14:36 +00:00
Commented Mar 3, 2013 at 14:14 — Pieter Geerkens
– Pieter Geerkens, Commented Mar 3, 2013 at 14:14
Of course they are - I just looked at the code again.

Pieter Geerkens
– Pieter Geerkens

2013-03-03 14:15:29 +00:00
Commented Mar 3, 2013 at 14:15 — Pieter Geerkens
– Pieter Geerkens, Commented Mar 3, 2013 at 14:15
Could you provide sample data (sqlfiddle?).

Jakub Kania
– Jakub Kania

2013-03-03 14:32:55 +00:00
Commented Mar 3, 2013 at 14:32 — Jakub Kania
– Jakub Kania, Commented Mar 3, 2013 at 14:32

Pieter Geerkens · Accepted Answer · 2013-03-03 15:27:06Z

1

Try this (untested):

WITH cte(MaxField1) AS (
    SELECT (MAX(Field1)/10) AS MaxField1 FROM TABLE1
)
SELECT 
    'Part',
    COUNT(*)
FROM (
    SELECT 
        CONCAT((MaxField1*M.bucket), ' - ', (MaxField1*(M.bucket+1))) AS 'Part',
        Field1 / cte.MaxField1 AS bucket
    FROM TABLE1, cte
) AS M
GROUP BY 'Part'

For MySQL users, who don't have CTE expressions, try this instead:

SELECT 
    'Part',
    COUNT(*)
FROM (
    SELECT 
        CONCAT((MaxField1*M.bucket), ' - ', (MaxField1*(M.bucket+1))) AS 'Part',
        Field1 / cte.MaxField1 AS bucket
    FROM TABLE1, 
   (SELECT (MAX(Field1)/10) AS MaxField1 FROM TABLE1)cte
) AS M
GROUP BY 'Part'

OR in Dutch:

SELECT 
    'Part', 
    COUNT(*) 
FROM ( 
    SELECT 
        CONCAT((MaxPercbeheer*M.bucket), ' - ', (MaxPercbeheer*(M.bucket+1))) AS 'Part', 
        (Percbeheer/cte.MaxPercbeheer) AS bucket, 
        MaxPercbeheer 
    FROM POLIS, (SELECT (MAX(Percbeheer)/10) AS MaxPercbeheer FROM POLIS) cte
) AS M 
GROUP BY 'Part'

edited Mar 3, 2013 at 15:27

answered Mar 3, 2013 at 14:25

Pieter Geerkens

11.9k2 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

The Code Buccaneer Over a year ago

It says Unknown column "bucket" in fieldlist

Pieter Geerkens Over a year ago

@The Code Buccaneer: I modified a second time. Did you catch the latest update?

Pieter Geerkens Over a year ago

Is this not a valid construct in MySQL perhaps: SELECT bucket = Field1 / cte.MaxField1? Let's reword it using AS

The Code Buccaneer Over a year ago

I have it working with the following script: SELECT CONCAT((MaxPercbeheerM.bucket), ' - ', (MaxPercbeheer*(M.bucket+1))) AS 'Part', COUNT() FROM ( SELECT (Percbeheer/cte.MaxPercbeheer) AS bucket, MaxPercbeheer FROM POLIS, (SELECT (MAX(Percbeheer)/10) AS MaxPercbeheer FROM POLIS)cte ) AS M GROUP BY bucket But the buckets are not correct. There are 49 rows instead of 10

Pieter Geerkens Over a year ago

I know what I forgot. Give me a minute

slaakso · Accepted Answer · 2013-03-03 16:03:44Z

0

You could turn it into one query as the union parts are essentially same query:

create temporary table range_tmp (start int, end int);
insert into range_tmp values (0,1),(1,2),(2,3),(3,4),(4,5),(5,6),(7,8),(9,10);

SELECT CONCAT((M.MaxField1*t.start), ' - ', (M.MaxField1*t.end)) AS 'Part',COUNT(*)
FROM TABLE1 P, (SELECT (MAX(Field1)*(1/10)) AS MaxField1 FROM TABLE1) AS M, range_tmp t
WHERE (P.Field1>=(M.MaxField1*t.start) or t.start=1) AND (P.Field1<(M.MaxField1*t.end) or t.end=10)

answered Mar 3, 2013 at 16:03

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

Optimizing my sql query

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related