Azhar Rahi Wrote:
-------------------------------------------------------
> I am doing this
>
> INNER Join IN_ROUTE on if(p_CarrierType = 0,
> axecdr.IN_Route = IN_ROUTE.VALUE, 1)
>
> INNER JOIN OUT_ROUTE ON if(p_CarrierType = 1,
> axecdr.OUT_Route = OUT_ROUTE.VALUE, 1)
>
No, no, no, join won't work that way.
If you really want to do this "conditional" join, try to do it in this way:
set @p_CarrierType = 1;
select *
from axecdr
JOIN
(
select * from out_route
where @p_CarrierType = 1
UNION ALL
select null from dual
where @p_CarrierType <> 1
) b
ON ( @p_CarrierType <> 1 OR axecdr.OUT_ROUTE = b.value )
One thing is important - values in the OUT_ROUTE table
must be unique,
in other case you will get wrong results from the join. The best way to enforce this is to create the primary key on the value column or the unique index.
And the other thing - again and again - if you don't
create an indexes (or PK) on value column in OUT_ROUTE and IN_ROUTE, this query won't perform the fasted way, whether you will use JOIN or IN or EXIST or something else.
> One thing which made me surprised is a field DUR
> (INT(11) NULLABLE). When I include this field the
> response time is 50 seconds, but when I exclude
> it, the response time became 12 seconds. Is there
> any problem with this Field. Should it not
> NULLABLE. Because when I run the query with id
> (bigint(20) Primary Key), its response time is
> still 12 seconds.
Did you include new fields in your query ?
If yes, then please show us the exact query do you actually want to optimize.
Nobody can guess what do you see on your monitor, without knowing it is really hard to help you.
I don't see your monitor, and cannot