Re: MySQL Question

From: "John Taylor-Johnston" <taylorjo@collegesherbrooke.qc.ca> > Sorry, don't want to be off-topic, but have found something curious about MySQL 4.0.16-standard. > > It does not seem to prioritise properly. Searching for 'English Canada' > (as opposed to +English +Canada) > gives me all instances of both words but does not prioritize the display order for > 'English Canada' first and then 'English' then 'Canada'. And how would you know that? > SELECT * FROM ccl_main WHERE MATCH > (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,KW,AUS,GEO,AN,RB,CO) AGAINST ('English > Canada' IN BOOLEAN MODE) > ORDER BY id asc; > > versus > > SELECT * FROM ccl_main WHERE MATCH > (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,KW,AUS,GEO,AN,RB,CO) AGAINST ('English > Canada' IN BOOLEAN MODE); > > gives the same order. Given these queries, you're not ordering by the relevance MySQL determines, so you really don't know. You need to also use your MATCH ... AGAINST condition in the SELECT columns and then order by that. SELECT *, MATCH (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,KW,AUS,GEO,AN,RB,CO) AGAINST ('English Canada' IN BOOLEAN MODE) AS relevancy FROM ccl_main WHERE MATCH (YR,AU,ST,SD,SC,BT,BD,BC,AT,AD,AC,KW,AUS,GEO,AN,RB,CO) AGAINST ('English Canada' IN BOOLEAN MODE) ORDER BY relevancy DESC; Now I can't honestly say that MySQL determines "English Canada" is more relevant than the two words found by themselves, but this will show you whether it does or not. ---John Holmes...