From: mos (mos99fastmail.fm)
Date: Thu Mar 12 2009 - 11:56:44 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

I have a view which is a 3 table join on a compound index.
I have two indexes: Index1: Product_Code, Store_Id, Date_Sold and
                                        Index2:
Date_Sold,Store_Id,Product_Code

If I execute a select like:

select * from MyView where product_code="123";

it returns the results in 3 seconds from 15 million rows because it uses
index 1.

But if I execute

select * from MyView where Date_Sold="2009-03-10";
for some reason it is still trying to use Index1 and it takes forever to
return the results because it is doing a full table scan to join the 3 tables.

Is there a way to force the view to use Index2?

Unfortunately
    select * from MyView use index(Index2) where Date_Sold="2009-03-10";
throws an error.

I think the only way around it is to create another view and put the "Use
Index(Index2)" into the view itself and then call the appropriate view
depending on the Where clause of the Select statement.
Does this make sense? Or is there a better way?

TIA
Mike

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]