Infobright.org Forums |

UNION ALL very slow

Posted: 25 February 2010 07:03 PM

[ Ignore ]

Newbie

Total Posts: 3

Joined 2010-02-25

When I run the following query UNION ALL I get the 1000 rows in 25-30 seconds. I’ve reduced the query to its simplest form for demonstration purposes.

SELECT z.account_number
FROM tickets.order_tickets_200911 z
WHERE z.order_date >= '2009-11-16 00:00:00'
  and z.order_date <= '2009-11-16 23:59:59'
UNION ALL
SELECT e.account_number
  FROM tickets.execution_tickets_200911 e
 WHERE e.execution_date >= '2009-11-16 00:00:00'
   AND e.execution_date <= '2009-11-16 23:59:59'
LIMIT 1000 

From bh.err for the UNION:

2010-02-25 17:42:51 [730] T:-1 = TABLE_ALIAS(T:0,“order_tickets_200911”)
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:4))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,“account_number”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
VC:-2.2 = CREATE_VC(T:-2,EXPR(“2009-11-16 00:00:00”))
F:0 = CREATE_FILTER(T:-2,VC:-2.1,>=,VC:-2.2,<null>,WHERE)
VC:-2.3 = CREATE_VC(T:-2,EXPR(“2009-11-16 23:59:59”))
F:0 = AND(F:0,VC:-2.1,<=,VC:-2.3,<null>)
T:-2.FILTER(F:0)
T:-3 = TABLE_ALIAS(T:1,“execution_tickets_200911”)
T:-4 = TMP_TABLE(T:-3)
VC:-4.0 = CREATE_VC(T:-4,PHYS_COL(T:-3,A:2))
A:-1 = T:-4.ADD_COLUMN(VC:-4.0,LIST,“account_number”,“ALL”)
VC:-4.1 = CREATE_VC(T:-4,PHYS_COL(T:-3,A:1))
VC:-4.2 = CREATE_VC(T:-4,EXPR(“2009-11-16 00:00:00”))
F:1 = CREATE_FILTER(T:-4,VC:-4.1,>=,VC:-4.2,<null>,WHERE)
VC:-4.3 = CREATE_VC(T:-4,EXPR(“2009-11-16 23:59:59”))
F:1 = AND(F:1,VC:-4.1,<=,VC:-4.3,<null>)
T:-4.FILTER(F:1)
T:-2 = UNION(T:-2,T:-4,1)
T:-2.MODE(LIMIT,0,1000)
RESULT(T:-2)

2010-02-25 17:42:51 Type conversion for VC:2
2010-02-25 17:42:51 Type conversion for VC:3
2010-02-25 17:42:51 [730] Merging conditions…
2010-02-25 17:42:51 [730] Initial execution plan (non-join):
2010-02-25 17:42:51 [730] Cnd(0): VC:1(t0a0) BET. VC:6 AND VC:9 (17.29)
2010-02-25 17:42:51 [730] Packs/packrows after KN evaluation:
2010-02-25 17:42:51 [730] (t0) Pckrows: 9758, susp. 19 (9242 empty 497 full). Packs opened in 1 cond.: 19
2010-02-25 17:42:51 [730] Packrows after exact evaluation (WHERE):
2010-02-25 17:42:51 [730] (t0): 9758 all packrows, 516 to open (including 497 full)
2010-02-25 17:42:51 Type conversion for VC:2
2010-02-25 17:42:51 Type conversion for VC:3
2010-02-25 17:42:51 [730] Merging conditions…
2010-02-25 17:42:51 [730] Initial execution plan (non-join):
2010-02-25 17:42:51 [730] Cnd(0): VC:1(t0a1) BET. VC:6 AND VC:9 (12.69)
2010-02-25 17:42:51 [730] Packs/packrows after KN evaluation:
2010-02-25 17:42:51 [730] (t0) Pckrows: 142, susp. 4 (133 empty 5 full). Packs opened in 1 cond.: 4
2010-02-25 17:42:51 [730] Packrows after exact evaluation (WHERE):
2010-02-25 17:42:51 [730] (t0): 142 all packrows, 9 to open (including 5 full)

But running each portion separately, the queries are sub-second? Why? Even with more criteria, joins to dimension tables and 10 or 11 columns the individual queries take but a few seconds while the UNION ALL never finishes.

From bh.err (first query):

2010-02-25 17:45:07 [736] T:-1 = TABLE_ALIAS(T:0,“order_tickets_200911”)
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:4))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,“account_number”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
VC:-2.2 = CREATE_VC(T:-2,EXPR(“2009-11-13 00:00:00”))
F:0 = CREATE_FILTER(T:-2,VC:-2.1,>=,VC:-2.2,<null>,WHERE)
VC:-2.3 = CREATE_VC(T:-2,EXPR(“2009-11-13 23:59:59”))
F:0 = AND(F:0,VC:-2.1,<=,VC:-2.3,<null>)
T:-2.FILTER(F:0)
T:-2.MODE(LIMIT,0,1000)
RESULT(T:-2)

2010-02-25 17:45:07 Type conversion for VC:2
2010-02-25 17:45:07 Type conversion for VC:3
2010-02-25 17:45:07 [736] Merging conditions…
2010-02-25 17:45:07 [736] Initial execution plan (non-join):
2010-02-25 17:45:07 [736] Cnd(0): VC:1(t0a0) BET. VC:6 AND VC:9 (17.22)
2010-02-25 17:45:07 [736] Packs/packrows after KN evaluation:
2010-02-25 17:45:07 [736] (t0) Pckrows: 9758, susp. 19 (9276 empty 463 full). Packs opened in 1 cond.: 19
2010-02-25 17:45:07 [736] Packrows after exact evaluation (WHERE):
2010-02-25 17:45:07 [736] (t0): 9758 all packrows, 482 to open (including 463 full)
2010-02-25 17:45:07 [736] Displaying result: 1000 rows.
2010-02-25 17:45:07 [736] Total data packs actually loaded (approx.): 9
2010-02-25 17:45:07 [736]——————————————————————————————————————

From bh.err (second query):

2010-02-25 17:47:05 [736] T:-1 = TABLE_ALIAS(T:0,“execution_tickets_200911”)
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:2))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,“account_number”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:1))
VC:-2.2 = CREATE_VC(T:-2,EXPR(“2009-11-16 00:00:00”))
F:0 = CREATE_FILTER(T:-2,VC:-2.1,>=,VC:-2.2,<null>,WHERE)
VC:-2.3 = CREATE_VC(T:-2,EXPR(“2009-11-16 23:59:59”))
F:0 = AND(F:0,VC:-2.1,<=,VC:-2.3,<null>)
T:-2.FILTER(F:0)
T:-2.MODE(LIMIT,0,1000)
RESULT(T:-2)

2010-02-25 17:47:05 Type conversion for VC:2
2010-02-25 17:47:05 Type conversion for VC:3
2010-02-25 17:47:05 [736] Merging conditions…
2010-02-25 17:47:05 [736] Initial execution plan (non-join):
2010-02-25 17:47:05 [736] Cnd(0): VC:1(t0a1) BET. VC:6 AND VC:9 (12.69)
2010-02-25 17:47:05 [736] Packs/packrows after KN evaluation:
2010-02-25 17:47:05 [736] (t0) Pckrows: 142, susp. 4 (133 empty 5 full). Packs opened in 1 cond.: 4
2010-02-25 17:47:05 [736] Packrows after exact evaluation (WHERE):
2010-02-25 17:47:05 [736] (t0): 142 all packrows, 9 to open (including 5 full)
2010-02-25 17:47:05 [736] Displaying result: 1000 rows.
2010-02-25 17:47:05 [736] Total data packs actually loaded (approx.): 0
2010-02-25 17:47:05 [736]——————————————————————————————————————

Some vitals on our system:

tickets.order_tickets_200911 - 639467519 records
tickets.execution_tickets_200911 - 9290427 records

Red Hat Enterprise Linux Server release 5.1 / 64Gb RAM
IB_3.2_GA_5316(ice)
data and cache directories over NFS (Netapp SAN)

ServerMainHeapSize = 24000
ServerCompressedHeapSize = 4000
LoaderMainHeapSize = 1200

Thanks for the help!

Profile

Jakub Wroblewski

Posted: 26 February 2010 02:51 AM

[ Ignore ] [ # 1 ]

Sr. Member

Total Posts: 736

Joined 2008-08-18

Hi,

Please try to LIMIT 1000 both parts of unioned results. Without that, the original query have to create a big result of both subqueries (millions of rows), then do union, then limit it. On the other hand, the queries running separately produces only 1000 rows both.

Regards,

Signature

Profile

MrFluffy

Posted: 26 February 2010 12:55 PM

[ Ignore ] [ # 2 ]

Newbie

Total Posts: 3

Joined 2010-02-25

The LIMIT was in there for testing purposes but regardless I tried your suggestion and it worked for the simplified query I supplied. However, the query below is a better example of what I’m trying to do and with the LIMIT on both portions of the UNION it’s still very, very slow (never completes). What’s most interesting is the line after the inner join that says “Produced 7207586 tuples.”. When I run just the orders portions of the UNION that same message says “Produced 1000 tuples” with the prior two lines being identical.

SELECT ad.adesc AS adesc,
       DATE_FORMAT(e.execution_date,'%m/%d/%Y %H:%i:%s') AS event_timestamp,
       e.order_id AS sort_key,
       0 + DATE_FORMAT(e.execution_date,'%H%i%s') AS time_sort,
       3 AS weight,
       et.venue AS venue,
       e.account_number AS account,
       e.trans AS action,
       e.shares AS qty,
       e.symbol AS symbol,
       e.order_number AS order_number
  FROM tickets.execution_tickets_200911 e
       JOIN mis_archive.acctdim_history ad ON e.account_number = ad.acct AND ad.data_date = '2009-12-31'
       JOIN mis_archive.exec_type_venues et ON e.exec_type = et.exec_type
 WHERE e.execution_date >= '2009-11-17 00:00:00'
   AND e.execution_date <= '2009-11-17 23:59:59'
   AND e.exec_type not in ('@','X','R','o','B')
   AND eid <> 91
 LIMIT 1000
UNION ALL
SELECT ad.adesc AS adesc,
       DATE_FORMAT(z.order_date,'%m/%d/%Y %H:%i:%s') AS event_timestamp,
       CASE WHEN IFNULL(cancel_number,1)=1 THEN order_id ELSE cancel_id END AS sort_key,
       0 + DATE_FORMAT(z.order_date,'%H%i%s') AS time_sort,
       CASE WHEN IFNULL(cancel_number,1) THEN 1 ELSE 2 END AS weight,
       et.venue AS venue,
       z.account_number AS account,
       z.order_inst AS action,
       z.shares AS qty,
       z.symbol AS symbol,
       z.order_number AS order_number
  FROM tickets.order_tickets_200911 z
       JOIN mis_archive.acctdim_history ad ON z.account_number = ad.acct AND ad.data_date = '2009-12-31'
       JOIN mis_archive.exec_type_venues et ON z.exec_type = et.exec_type
 WHERE z.order_date >= '2009-11-17 00:00:00'
   AND z.order_date <= '2009-11-17 23:59:59'
   AND z.exec_type NOT IN ('@','X','R','o','s','O','B')
   AND eid <> 91
 LIMIT 1000 

2010-02-26 11:41:26 [917] T:-1 = TABLE_ALIAS(T:0,“execution_tickets_200911”)
T:-2 = TMP_TABLE(T:-1)
T:-3 = TABLE_ALIAS(T:1,“acctdim_history”)
T:-2.JOIN(T:-3)
T:-4 = TABLE_ALIAS(T:2,“exec_type_venues”)
T:-2.JOIN(T:-4)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:2))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,“adesc”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,EXPR(“date_format”))
A:-2 = T:-2.ADD_COLUMN(VC:-2.1,LIST,“event_timestamp”,“ALL”)
VC:-2.2 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:6))
A:-3 = T:-2.ADD_COLUMN(VC:-2.2,LIST,“sort_key”,“ALL”)
VC:-2.3 = CREATE_VC(T:-2,EXPR(”+”))
A:-4 = T:-2.ADD_COLUMN(VC:-2.3,LIST,“time_sort”,“ALL”)
VC:-2.4 = CREATE_VC(T:-2,EXPR(“3”))
A:-5 = T:-2.ADD_COLUMN(VC:-2.4,LIST,“weight”,“ALL”)
VC:-2.5 = CREATE_VC(T:-2,PHYS_COL(T:-4,A:1))
A:-6 = T:-2.ADD_COLUMN(VC:-2.5,LIST,“venue”,“ALL”)
VC:-2.6 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:2))
A:-7 = T:-2.ADD_COLUMN(VC:-2.6,LIST,“account”,“ALL”)
VC:-2.7 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:7))
A:-8 = T:-2.ADD_COLUMN(VC:-2.7,LIST,“action”,“ALL”)
VC:-2.8 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:8))
A:-9 = T:-2.ADD_COLUMN(VC:-2.8,LIST,“qty”,“ALL”)
VC:-2.9 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:9))
A:-10 = T:-2.ADD_COLUMN(VC:-2.9,LIST,“symbol”,“ALL”)
VC:-2.10 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:19))
A:-11 = T:-2.ADD_COLUMN(VC:-2.10,LIST,“order_number”,“ALL”)
VC:-2.11 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:1))
VC:-2.12 = CREATE_VC(T:-2,EXPR(“2009-11-17 00:00:00”))
F:0 = CREATE_FILTER(T:-2,VC:-2.11,>=,VC:-2.12,<null>,WHERE)
VC:-2.13 = CREATE_VC(T:-2,EXPR(“2009-11-17 23:59:59”))
F:0 = AND(F:0,VC:-2.11,<=,VC:-2.13,<null>)
VC:-2.14 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:5))
VC:-2.15 = CREATE_VC(T:-2,EXPR(”@”))
VC:-2.16 = CREATE_VC(T:-2,EXPR(“X”))
VC:-2.17 = CREATE_VC(T:-2,EXPR(“R”))
VC:-2.18 = CREATE_VC(T:-2,EXPR(“o”))
VC:-2.19 = CREATE_VC(T:-2,EXPR(“B”))
VC:-2.20 = CREATE_VC(T:-2,VIRT_COLS([15,..,19](5 items)))
F:0 = AND(F:0,VC:-2.14,NOT IN,VC:-2.20,<null>)
VC:-2.21 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:10))
VC:-2.22 = CREATE_VC(T:-2,EXPR(“91”))
F:0 = AND(F:0,VC:-2.21,<>,VC:-2.22,<null>)
VC:-2.23 = CREATE_VC(T:-2,PHYS_COL(T:-4,A:0))
F:0 = AND(F:0,VC:-2.14,=,VC:-2.23,<null>)
VC:-2.24 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:1))
F:0 = AND(F:0,VC:-2.6,=,VC:-2.24,<null>)
VC:-2.25 = CREATE_VC(T:-2,PHYS_COL(T:-3,A:0))
VC:-2.26 = CREATE_VC(T:-2,EXPR(“2009-12-31”))
F:0 = AND(F:0,VC:-2.25,=,VC:-2.26,<null>)
T:-2.FILTER(F:0)
T:-2.MODE(LIMIT,0,1000)
T:-5 = TABLE_ALIAS(T:3,“order_tickets_200911”)
T:-6 = TMP_TABLE(T:-5)
T:-7 = TABLE_ALIAS(T:1,“acctdim_history”)
T:-6.JOIN(T:-7)
T:-8 = TABLE_ALIAS(T:2,“exec_type_venues”)
T:-6.JOIN(T:-8)
VC:-6.0 = CREATE_VC(T:-6,PHYS_COL(T:-7,A:2))
A:-1 = T:-6.ADD_COLUMN(VC:-6.0,LIST,“adesc”,“ALL”)
VC:-6.1 = CREATE_VC(T:-6,EXPR(“date_format”))
A:-2 = T:-6.ADD_COLUMN(VC:-6.1,LIST,“event_timestamp”,“ALL”)
VC:-6.2 = CREATE_VC(T:-6,EXPR(“case”))
A:-3 = T:-6.ADD_COLUMN(VC:-6.2,LIST,“sort_key”,“ALL”)
VC:-6.3 = CREATE_VC(T:-6,EXPR(”+”))
A:-4 = T:-6.ADD_COLUMN(VC:-6.3,LIST,“time_sort”,“ALL”)
VC:-6.4 = CREATE_VC(T:-6,EXPR(“case”))
A:-5 = T:-6.ADD_COLUMN(VC:-6.4,LIST,“weight”,“ALL”)
VC:-6.5 = CREATE_VC(T:-6,PHYS_COL(T:-8,A:1))
A:-6 = T:-6.ADD_COLUMN(VC:-6.5,LIST,“venue”,“ALL”)
VC:-6.6 = CREATE_VC(T:-6,PHYS_COL(T:-5,A:4))
A:-7 = T:-6.ADD_COLUMN(VC:-6.6,LIST,“account”,“ALL”)
VC:-6.7 = CREATE_VC(T:-6,PHYS_COL(T:-5,A:6))
A:-8 = T:-6.ADD_COLUMN(VC:-6.7,LIST,“action”,“ALL”)
VC:-6.8 = CREATE_VC(T:-6,PHYS_COL(T:-5,A:7))
A:-9 = T:-6.ADD_COLUMN(VC:-6.8,LIST,“qty”,“ALL”)
VC:-6.9 = CREATE_VC(T:-6,PHYS_COL(T:-5,A:8))
A:-10 = T:-6.ADD_COLUMN(VC:-6.9,LIST,“symbol”,“ALL”)
VC:-6.10 = CREATE_VC(T:-6,PHYS_COL(T:-5,A:2))
A:-11 = T:-6.ADD_COLUMN(VC:-6.10,LIST,“order_number”,“ALL”)
VC:-6.11 = CREATE_VC(T:-6,PHYS_COL(T:-5,A:0))
VC:-6.12 = CREATE_VC(T:-6,EXPR(“2009-11-17 00:00:00”))
F:1 = CREATE_FILTER(T:-6,VC:-6.11,>=,VC:-6.12,<null>,WHERE)
VC:-6.13 = CREATE_VC(T:-6,EXPR(“2009-11-17 23:59:59”))
F:1 = AND(F:1,VC:-6.11,<=,VC:-6.13,<null>)
VC:-6.14 = CREATE_VC(T:-6,PHYS_COL(T:-5,A:14))
VC:-6.15 = CREATE_VC(T:-6,EXPR(”@”))
VC:-6.16 = CREATE_VC(T:-6,EXPR(“X”))
VC:-6.17 = CREATE_VC(T:-6,EXPR(“R”))
VC:-6.18 = CREATE_VC(T:-6,EXPR(“o”))
VC:-6.19 = CREATE_VC(T:-6,EXPR(“s”))
VC:-6.20 = CREATE_VC(T:-6,EXPR(“O”))
VC:-6.21 = CREATE_VC(T:-6,EXPR(“B”))
VC:-6.22 = CREATE_VC(T:-6,VIRT_COLS([15,..,21](7 items)))
F:1 = AND(F:1,VC:-6.14,NOT IN,VC:-6.22,<null>)
VC:-6.23 = CREATE_VC(T:-6,PHYS_COL(T:-7,A:10))
VC:-6.24 = CREATE_VC(T:-6,EXPR(“91”))
F:1 = AND(F:1,VC:-6.23,<>,VC:-6.24,<null>)
VC:-6.25 = CREATE_VC(T:-6,PHYS_COL(T:-8,A:0))
F:1 = AND(F:1,VC:-6.14,=,VC:-6.25,<null>)
VC:-6.26 = CREATE_VC(T:-6,PHYS_COL(T:-7,A:1))
F:1 = AND(F:1,VC:-6.6,=,VC:-6.26,<null>)
VC:-6.27 = CREATE_VC(T:-6,PHYS_COL(T:-7,A:0))
VC:-6.28 = CREATE_VC(T:-6,EXPR(“2009-12-31”))
F:1 = AND(F:1,VC:-6.27,=,VC:-6.28,<null>)
T:-6.FILTER(F:1)
T:-2 = UNION(T:-2,T:-6,1)
T:-2.MODE(LIMIT,0,1000)
RESULT(T:-2)

2010-02-26 11:41:26 Type conversion for VC:12
2010-02-26 11:41:26 Type conversion for VC:13
2010-02-26 11:41:26 Type conversion for VC:26
2010-02-26 11:41:26 [917] Adding 1 conditions…
2010-02-26 11:41:26 [917] Merging conditions…
2010-02-26 11:41:26 [917] Initial execution plan (non-join):
2010-02-26 11:41:26 [917] Cnd(0): VC:25(t1a0) BET. VC:36 AND VC:37 (0)
2010-02-26 11:41:26 [917] Cnd(1): VC:23(t2a0) NOT IN VC:20 (3.36)
2010-02-26 11:41:26 [917] Cnd(2): VC:11(t0a1) BET. VC:30 AND VC:33 (12.69)
2010-02-26 11:41:26 [917] Cnd(3): VC:21(t1a10) NOT BET. VC:34(91) AND VC:35(91) (12.91)
2010-02-26 11:41:26 [917] Cnd(4): VC:14(t0a5) NOT IN VC:20 (15.35)
2010-02-26 11:41:26 [917] Packs/packrows after KN evaluation:
2010-02-26 11:41:26 [917] (t0) Pckrows: 142, susp. 8 (134 empty 0 full). Packs opened in 2 cond.: 11
2010-02-26 11:41:26 [917] (t1) Pckrows: 13, susp. 1 (12 empty 0 full). Packs opened in 2 cond.: 2
2010-02-26 11:41:26 [917] (t2) Pckrows: 1, susp. 1 (0 empty 0 full). Packs opened in 1 cond.: 1
2010-02-26 11:41:26 [917] Packrows after exact evaluation (WHERE):
2010-02-26 11:41:26 [917] (t0): 142 all packrows, 7 to open (including 0 full)
2010-02-26 11:41:26 [917] (t1): 13 all packrows, 1 to open (including 0 full)
2010-02-26 11:41:26 [917] (t2): 1 all packrows, 1 to open (including 0 full)
2010-02-26 11:41:26 [917] Join execution plan:
2010-02-26 11:41:26 [917] Cnd(5): VC:14(t0a5) = VC:23(t2a0) (24.3)
2010-02-26 11:41:26 [917] Cnd(6): VC:6(t0a2) = VC:24(t1a1) (33.82)
2010-02-26 11:41:26 [917] Traversed all 51 rows.
2010-02-26 11:41:27 [917] Produced 425273 tuples.
2010-02-26 11:41:27 [917] Tuples after inner join 0-2 [hash]: 425273
2010-02-26 11:41:27 [917] Traversed all 18899 rows.
2010-02-26 11:41:27 [917] Produced 1000 tuples.
2010-02-26 11:41:27 [917] Tuples after inner join 0-1 [hash]: 1000
2010-02-26 11:41:27 Type conversion for VC:12
2010-02-26 11:41:27 Type conversion for VC:13
2010-02-26 11:41:27 Type conversion for VC:28
2010-02-26 11:41:27 [917] Adding 1 conditions…
2010-02-26 11:41:27 [917] Merging conditions…
2010-02-26 11:41:27 [917] Initial execution plan (non-join):
2010-02-26 11:41:27 [917] Cnd(0): VC:27(t1a0) BET. VC:38 AND VC:39 (0)
2010-02-26 11:41:27 [917] Cnd(1): VC:25(t2a0) NOT IN VC:22 (3.36)
2010-02-26 11:41:27 [917] Cnd(2): VC:23(t1a10) NOT BET. VC:36(91) AND VC:37(91) (12.91)
2010-02-26 11:41:27 [917] Cnd(3): VC:11(t0a0) BET. VC:32 AND VC:35 (17.29)
2010-02-26 11:41:27 [917] Cnd(4): VC:14(t0a14) NOT IN VC:22 (19.58)
2010-02-26 11:41:27 [917] Packs/packrows after KN evaluation:
2010-02-26 11:41:27 [917] (t0) Pckrows: 9758, susp. 122 (9243 empty 393 full). Packs opened in 2 cond.: 140
2010-02-26 11:41:27 [917] (t1) Pckrows: 13, susp. 1 (12 empty 0 full). Packs opened in 2 cond.: 2
2010-02-26 11:41:27 [917] (t2) Pckrows: 1, susp. 1 (0 empty 0 full). Packs opened in 1 cond.: 1
2010-02-26 11:41:27 [917] Packrows after exact evaluation (WHERE):
2010-02-26 11:41:27 [917] (t0): 9758 all packrows, 507 to open (including 393 full)
2010-02-26 11:41:27 [917] (t1): 13 all packrows, 1 to open (including 0 full)
2010-02-26 11:41:27 [917] (t2): 1 all packrows, 1 to open (including 0 full)
2010-02-26 11:41:27 [917] Join execution plan:
2010-02-26 11:41:27 [917] Cnd(5): VC:14(t0a14) = VC:25(t2a0) (24.73)
2010-02-26 11:41:27 [917] Cnd(6): VC:6(t0a4) = VC:26(t1a1) (40.25)
2010-02-26 11:41:27 [917] Traversed all 49 rows.
2010-02-26 11:41:33 [917] Produced 33083430 tuples.
2010-02-26 11:41:34 [917] Tuples after inner join 0-2 [hash]: 33083430
2010-02-26 11:41:34 [917] Traversed all 18899 rows.
2010-02-26 11:41:39 [917] Produced 7207586 tuples.
2010-02-26 11:41:39 [917] Roughly omitted 2.56% packrows.
2010-02-26 11:41:39 [917] Tuples after inner join 0-1 [hash]: 7207586

Profile

Jakub Wroblewski

Posted: 26 February 2010 01:10 PM

[ Ignore ] [ # 3 ]

Sr. Member

Total Posts: 736

Joined 2008-08-18

Hi,

What about such schema:

(SELECT ... the first query ... LIMIT 1000)
UNION ALL
(SELECT ... the second query... LIMIT 1000)
LIMIT 1000; 

Regards,

Signature

Profile

MrFluffy

Posted: 26 February 2010 01:34 PM

[ Ignore ] [ # 4 ]

Newbie

Total Posts: 3

Joined 2010-02-25

That seemed to solve the slowness problem but now I’m getting errors like the following on most columns:

2010-02-26 12:27:54 [984] Switched to MySQL: UNION of non-matching columns (column no 5)

2010-02-26 12:29:17 [984] Switched to MySQL: Cannot UNION with a column wider then the current practical column width (column 3). Try changing order of union’ed tables)

Profile

Janusz Borkowski

Posted: 26 February 2010 04:40 PM

[ Ignore ] [ # 5 ]

Super Duper Member

Total Posts: 916

Joined 2008-08-18

Hi !

The limitation is already fixed, but I am not sure if it is contained in the last ICE release. You can try using CAST to make the type same. E.g. cast a character field to a wider one in the first query, so all values from the second query will fit there. Also, you should cast numerical types to a common one, e.g. INT may not be allowed to be UNIONed with DECIMAL(10,1), but if it is casted to DECIMAL(10,1) it should work.

Profile

‹‹ "Lost connection to MySQL server during query" frequently ICE Installation/startup errors ››

Username:
Password:
Auto-login on future visits
Forgot your password?