Hi list.
I ran into interesting issue with one query. Here is
simplified DDL:
CREATE TABLE TMP_1
(
A_TYPE FIXED (10) NOT NULL,
ID_1 FIXED (19) NOT NULL,
ID_2 FIXED (19) NOT NULL,
A_TARGET FIXED (1) NOT NULL
)
CREATE INDEX TMP_1_IDX_TYPE ON TMP_1 (A_TYPE)
CREATE INDEX TMP_1_IDX_VAL ON TMP_1 (ID_1, ID_2)
Table TMP_1 has about 60000 records and statistics is
updated. The query is:
select ID_1, ID_2, A_TARGET
from TMP_1
where A_TYPE = 0
and
(
(
ID_1 = 5630570702179745801
and ID_2 = 212463839891505161
and A_TARGET = 0
)
or
(
ID_2 = -1
and
(
(
ID_1 = 5630570702179745801
and A_TARGET = 1
)
or
(
ID_1 = 212463839891505161
and A_TARGET = 2
)
)
)
)
And explain for this query results full table scan.
But if I really slightly twick this query like this:
select ID_1, ID_2, A_TARGET
from TMP_1
where A_TYPE = 0
and
(
(
ID_1 = 5630570702179745801
and ID_2 = 212463839891505161
and A_TARGET = 0
)
or
(
(
ID_2 = -1
and ID_1 = 5630570702179745801
and A_TARGET = 1
)
or
(
ID_2 = -1
and ID_1 = 212463839891505161
and A_TARGET = 2
)
)
)
It starts using all needed indexes and works way
faster. The difference between first and second
queries is just that the ID_2 = -1 condition is put
under both parts of or-condition in the second one and
left out as a common part in the first. Can anyone
look at it?
Best regards.
Alexei Novakov
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
