On Monday, June 07, 2004 20:23, Justin Swanhart wrote >> It can only ever return one row from table b, for >> each row on table a, due >> to the contents that is stored in table b. The >> table contains in excess of >> a million records. What happens as a result of the
>I assume that you have multiple rows in table b that >match the primary key on table a, but only one of >those rows in b will be satisfied by your between >condition of the query. Hello Justin, fisrtly, thank you for your assistance. Then, as I posted previously, keys do not match, however, a property on table a matches only in respect of being between two properties on table b. >First off, make sure that you have an index on table b >that matches the primary key in table a. Make sure >the data types and lengths are the same for both keys. Please see previous post. > Secondly, make sure that your key cache is large > enough so that the keys to the tables are buffered. I'm not sure it is this. > If these assumptions are not correct, then you will > need to post the query, the explain of the query, and > the "show create table" for each table in the query so > that we have a better idea what you are dealing with. Please see previous post. >> I've looked in several books and searched Google but >> cannot get a way of >> doing this. It seems Oracle has a 'FIRST' in their >> select which they use >> for such a use case. But I do not see anything for >> MySql anywhere. > Oracle's FIRST_ROWS simply tells the optimizer to > prefer index scans over a FTS where lots of rows may > be returned by the query. It won't modify the > behavior of a query in the way you want it to. Thank you for info. > The only way I can think to implement the behavior > that you are looking for is to: > 1) get all the records from "a" that you need > 2) execute a second select for each row in "a" on "b" > with your between condition and a LIMIT clause so that > only one row is returned. Until 4.1 when subselects become avaialable, I suspect this is a workaround. > Still, if multiple rows > exist in "b" that match the PK on "a", those rows will > probably be scanned in filesort order, which means > that multiple rows will be looked at unless your > between matches the first row inserted, except if you > have an appropriate index. Please see previous post. Justin, again, thank you for your asistance. Kind regrds Emmanuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
