MySQL Forums
Forum List  »  Optimizer & Parser

Re: how to optimise query for 10+ millions records
Posted by: irek kordirko
Date: February 17, 2012 05:12PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: how to optimise query for 10+ millions records
1168
February 17, 2012 05:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.