OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
 
bad plan in case of joining a big table with empty temporary table

From: Alexey Gaidukov (gaiddc.baikal.ru)
Date: Sat May 27 2006 - 06:08:27 CDT

MaxDB 7.6.00.27

The testcase

gis.invest is a table with millions records. The field COUNTER is the
PRIMARY KEY

create table temp.test (
id integer
)
//
explain
select * from gis.invest,temp.test where counter=id
for reuse

TEMP TEST TABLE SCAN 0
GIS INVEST COUNTER JOIN VIA KEY COLUMN 69584
                            NO TEMPORARY RESULTS CREATED
                            RESULT IS COPIED , COSTVALUE IS > 2 E10

insert into temp.test values(1)
//
explain
select * from gis.invest,temp.test where counter=id
for reuse

TEMP TEST TABLE SCAN 0
GIS INVEST COUNTER JOIN VIA KEY COLUMN 69584
                            NO TEMPORARY RESULTS CREATED
                            RESULT IS COPIED , COSTVALUE IS 2

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb