On 5/17/05, Jigal van Hemert <[EMAIL PROTECTED]> wrote: > 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. >
Thanks for your response, your thoughts more or less confirm my own thinking about this. I can add indexes easily enough, the trouble with the indexes is that we're a little too early in the project to be able to tell which columns or groups of columns will make good indexes....well, there are *some* data columns that we know will make good indexes but there are number of others that are still up in the air. Cheers, Rod -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
