Hi! I've created the following query (and without explain it's so fast!!!):
EXPLAIN
SELECT P.ID, P.NAME, P.TYPE, P.STATUS, P.OLD_ID,
OI.SITE, OI.EMAIL, OI.BIRTHDAY, OI.FUNDS, OI.NUMOFPEOPLE,
OI.SIZE, PP.SIZE_DETAILS
FROM TABLE_A P
LEFT OUTER JOIN TABLE_B OI
ON P.ID = OI.ID
LEFT OUTER JOIN TABLE_C PP
ON OI.SIZE = PP.SIZE
WHERE P.ID < 1100
And I get the follwoing explain:
P RANGE CONDITION FOR KEY COLUMN 1
ID (USED KEY COLUMN)
OI ID JOIN VIA KEY COLUMN 549
PP SIZE JOIN VIA KEY COLUMN 245
MGR RESULT IS COPIED , COSTVALUE IS 9
Ok, it�s what I expected. But now I do
CREATE VIEW VI_TESTE AS
SELECT P.ID, P.NAME, P.TYPE, P.STATUS, P.OLD_ID,
OI.SITE, OI.EMAIL, OI.BIRTHDAY, OI.FUNDS, OI.NUMOFPEOPLE,
OI.SIZE, PP.SIZE_DETAILS
FROM TABLE_A P
LEFT OUTER JOIN TABLE_B OI
ON P.ID = OI.ID
LEFT OUTER JOIN TABLE_C PP
ON OI.SIZE = PP.SIZE
and then I issue
EXPLAIN
SELECT *
FROM VI_TESTE
WHERE ID < 1100
and the result is (and without explain, it's so slow!!!)
P TABLE SCAN
1
OI ID JOIN VIA KEY COLUMN 549
PP SIZE JOIN VIA KEY COLUMN 245
INTERNAL TEMPORARY RESULT TABLE SCAN
500
MGR RESULT IS COPIED , COSTVALUE IS
1559
why isn't index being used for P, contraring info found in docs:
http://www.sapdb.org/7.4/htmhelp/6d/117c44d14811d2a97400a0c9449261/content.htm
at paragraph
'The view table is always identical to the table that would be obtained as the result
of the
QUERY expression.'
Can someone help me point if this is a bug, or I'm doing something really wrong?
At time: I'm using 7.4.3.27 database.
Thanks for any help, or hints!
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
