From: "Rod Heyd" > Tables Version_(1-5) > > -------------------------------------------------------------------------- ---------------------------- > ID |PRODUCT_ID |DATA1 |DATA2 |DATA3 |.........|DATA_N | > -------------------------------------------------------------------------- ---------------------------- > > Table Preferred_Map > > ------------------------------------------------- > MAP_ID |ID |PRODUCT_ID | > ------------------------------------------------- >
> SELECT > Preferred_Map.PRODUCT_ID, DATA1, DATA2, DATA3 > FROM > Preferred_Map INNER JOIN Version_1 USING(ID,PRODUCT_ID) > WHERE > DATA1='x' AND DATA2='y' AND DATA3='z'; > > *************************** 1. row *************************** > id: 1 > select_type: SIMPLE > table: Preferred_Map > type: ALL > possible_keys: ID,PRODUCT_ID > key: NULL > key_len: NULL > ref: NULL > rows: 45191 > Extra: > *************************** 2. row *************************** > id: 1 > select_type: SIMPLE > table: Version_1 > type: eq_ref > possible_keys: PRIMARY,PRODUCT_ID > key: PRIMARY > key_len: 4 > ref: HiRISE_Test.Preferred_Map.ID > rows: 1 > Extra: Using where >From the explain and the data structure I must conclude that there is no index available that MySQL can use for the where clause. It will then use the estimated number of rows in the two tables to see which table it will start with. There seems to be no alternative then to first use the entire Preferred_Map table, join it with the Version_1 table and then do a table scan to select the rows. You should try to build your db and queries in such a way that there is a step in the the join where MySQL can easily select (using indexes) a very small set of records. Preferrably MySQL should have index to solve (part of) the WHERE conditions before joining the two tables. In that case Version_1 would have been the first table with a very low row count (depending on how much of the WHERE it could solve) and the row count of the second table would have been equally low, resulting in a blazingly fast query. Depending on how many DATA_N columns you have, how many distinct values are present in each table, which columns you will use, etc. you could add indexes for each DATE column or a few multiple-column indexes. If this could not solve the problem, maybe you should redesign the table and use a single field for the DATA and an extra field for the type of data. This requires extra joins, but you could ensure an appropriate index for each of the steps and thus the query could probably be quite fast. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
