mysql - most relevant results

[Parham]

is there a way to do a search through a table:

SELECT a,b,c,d,e,f,g FROM table WHERE a = "a" AND b = "b" AND c = "c" ...

and get the one where the most matched?  Of course after the WHERE clause, I wouldn't use the equal signs, but is there a way to try and get the most relevant table rows returned depending on what I'm searching for?

[[Unknown]]

You might try MATCH AGAINST, but that requires a full text index, and I'm not sure it would even do this.

Another VERY SLOW way is to do something like:

SELECT a, b, c, d, e, f, g
FROM table
ORDER BY a = 'a', b = 'b', c = 'c', d = 'd', etc.
LIMIT 1

But this would mean a (very) weighted search too.

You could also use or, and then manually check how many matched.

-[Unknown]

[Parham]

the problem with:

SELECT a, b, c, d, e, f, g
FROM table
ORDER BY a = 'a', b = 'b', c = 'c', d = 'd', etc.
LIMIT 1

is that nothing might match, and therefore the most relevant row wouldn't show up

i was thinking a sort of permutation idea (not quite a permutation, but i don't know what the word would be).

- select all, and try to match against all, most relevant
- remove 1 column, and match against all rest, second most relevant (this would require me to remove 1 column each time, 5 columns = 5 queries)
- remove 2 columns, and match against all rest, third most relevant (this would require me to remove 2 columns each time...)
- and so on

doing permutations of what to remove each time, and as it goes down, to remove more.  of course this would KILL resources if i had anything more than 5 columns, and even for 5 columns this is VERY heavy.

[Christian Land]

Just a little idea

Code Select Expand

SELECT a,b,c,IF(a='a',1,0)+IF(b='b',1,0)+IF(c='c',1,0) AS rel FROM testtab ORDER BY rel DESC LIMIT 1;

[Parham]

i'll try that (but it looks to make sense).  i've never done complicated statements like that.  i'm sure it'll work, and therefore:

if i knew you in person, i'm sure i'd kiss you right about now LOL

[Christian Land]

WAAAAAAHHHHHHHH!!!!

[Parham]

thank you VERY much SnowCrash

[Christian Land]

No problem... but I don't know if its very fast if your database contains a huge amount of data...

[Parham]

i can't imagine it'd be any slower... you aren't doing anything different from before, simply testing if values are equal, but instead of retrieving the value, just adding a number.  I mean it's at least better than everything I suggested above, which seems to drain the system.

lucky for me i'm taking a sql course this year which deals with optimization.  the problem is that it's an introductory course and right now (second week) we're only into nested SELECT statements.