Hi all,
hit the wrong key, sorry, about that....
here it goes again:
we run an sapdb instance version 7.3.0.24 on a w2k box as backend for our
content editing tools. One query is running a bit too slow so i would like
to ask, whether it could be reformulated to run faster.
The query in question is:
select
objloot.id,
objloot.counter,
objloot.itemid1,
itemnames1.content,
objloot.itemid2,
itemnames2.content,
objloot.itemid3,
itemnames2.content
from
objloot,
(select items.id id, lngstrings.content content from items join lngstrings
on items.string = lngstrings.id where language=0) itemnames1,
(select items.id id, lngstrings.content content from items join lngstrings
on items.string = lngstrings.id where language=0) itemnames2,
(select items.id id, lngstrings.content content from items join lngstrings
on items.string = lngstrings.id where language=0) itemnames3
where
objloot.id = 867 and
objloot.itemid1 = itemnames1.id (+)and
objloot.itemid2 = itemnames2.id (+)and
objloot.itemid3 = itemnames3.id (+)
order by counter
The explain statements show table scans for the three subqueries
DBA ITEMS TABLE SCAN
99
DBA LNGSTRINGS JOIN VIA MULTIPLE KEY
COLUMNS 955
ID (USED
KEY COLUMN)
LANGUAGE (USED KEY COLUMN)
DBA ITEMS TABLE SCAN
99
DBA LNGSTRINGS JOIN VIA MULTIPLE KEY
COLUMNS 955
ID (USED
KEY COLUMN)
LANGUAGE (USED KEY COLUMN)
DBA ITEMS TABLE SCAN
99
DBA LNGSTRINGS JOIN VIA MULTIPLE KEY
COLUMNS 955
ID (USED
KEY COLUMN)
LANGUAGE (USED KEY COLUMN)
DBA OBJLOOT RANGE CONDITION FOR
KEY COLUMN 13
ID (USED
KEY COLUMN)
INTERNAL TEMPORARY RESULT TABLE SCAN
500
INTERNAL TEMPORARY RESULT TABLE SCAN
500
INTERNAL TEMPORARY RESULT TABLE SCAN
500
DBA RESULT IS COPIED , COSTVALUE IS
10167149
The tables in question have the following fields (* marks primary key):
lngstrings: id (*), language(*), content
items: id(*), string (indexes into lngstrings)
objloot: id(*), counter(*), itemid1, itemid2, itemid3 (these three index
into items)
Thx,
Dirk
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
