Percent of match in condition

Hi All, I have a table with different fileds and almost the type of al=
l them are =0Asmallint.=A0I want to search on some fields with "OR" conditi=
on. I want to know is =0Athere possibility to know how many fileds matched =
exactly? then I can say how =0Amany percent match are available. selec=
t * from my_table where sex_id = 1 or country_id =3D 120 or education_i=
d I=A0mean if in my table there are some records with=A0sex_id = 1=
or country_id =3D =0A120=A0 then I got 2 (2 match) then I can say 66% perc=
ent match. Thank you, ali =0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Percent of match in condition

--20cf303ea25e0849f604932e6637
Content-Type: text/plain; charset=ISO-8859-1

Hmm. You might be able to hack this up using the if() function, but it's not
gonna be a beauty to look at, and possibly not terribly performant, either.

You may need to look at external data query tools - I think a number of
fulltext search tools provide match percentages in their results.


On Thu, Oct 21, 2010 at 3:57 PM, Ali A.F.N wrote:

> Hi All,
>
> I have a table with different fileds and almost the type of all them are
> smallint. I want to search on some fields with "OR" condition. I want to
> know is
> there possibility to know how many fileds matched exactly? then I can say
> how
> many percent match are available.
>
> select * from my_table where sex_id = 1 or country_id = 120 or education_id
>
> I mean if in my table there are some records with sex_id = 1 or country_id
> =
> 120 then I got 2 (2 match) then I can say 66% percent match.
>
> Thank you,
>
> ali
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--20cf303ea25e0849f604932e6637--

Re: Percent of match in condition

On 10/21/2010 9:57 AM, Ali A.F.N wrote:
> Hi All,
>
> I have a table with different fileds and almost the type of all them are
> smallint. I want to search on some fields with "OR" condition. I want to know is
> there possibility to know how many fileds matched exactly? then I can say how
> many percent match are available.
>
> select * from my_table where sex_id = 1 or country_id = 120 or education_id
>
> I mean if in my table there are some records with sex_id = 1 or country_id =
> 120 then I got 2 (2 match) then I can say 66% percent match.
>
> Thank you,

This is where having a name-value pair in your MySQL can help. Your data
appears to belong to a dating site but it could easily belong to a
product catalog or many other types of data sets. In this example,I want
to compute product matches to see how close they are to my search criteria.

In rough symbolic terms, this is one layout that can help.

item table
=================
id
product_name
(other fields)

item qualities table
=================
quality_id
item_id
quality_name
quality_value

An index on (quality_name, quality_value, item_id) also comes in very
handy right about now.

Let's say you wanted to look up all of the items that have color=blue,
size=90cm, and flavor=peach (I really cannot imagine what this product
may be, but after all this is only an example)

So, with a big wide table, you would need to either do something like

SELECT ... FROM old_style WHERE color='blue' and size='90cm' and
flavor='peach';

But that would only find you an exact match. For partial matches, you
would need to construct all sorts of partial queries. like

.... WHERE color='blue'
.... WHERE color='blue' and size='90cm'
.... WHERE color=size='90cm'
.... WHERE color=size='90cm' and flavor='peach'
....

and compare the results.

Using the new tables, you construct 3 union queries in your code and
cache the results in a temporary table:

CREATE TEMPORARY TABLE tmp_relevance ENGINE=MEMORY
(SELECT item_id FROM item_qualities WHERE color='blue')
UNION ALL
(SELECT item_id FROM item_qualities WHERE size='90cm')
UNION ALL
(SELECT item_id FROM item_qualities WHERE flavor='peach');

Then you count up how often each item_id was matched:

SELECT item_id, count(*) as frequency FROM tmp_relevance GROUP BY item_id;

Could even modify that last step to check for a threshold of matching
(say only those that match at least half of the terms you are looking
for) with something that looks like this

SELECT item_id, count(*) as frequency FROM tmp_relevance GROUP BY
item_id HAVING frequency/(#of terms in the search) >=0.5 ;

Of course, you know the value of (# of terms in the search) because
that's how many union queries you needed to run.

You can improve on this technique in many ways. Here is one from the top
of my head:

Instead of returning only an item_id in the first query, you can also
return a quality rating. Let's say you were looking for something sized
90cm and you only have 88cm pieces in stock, that may return a match
quality code of

1-(abs(90-88)/90)

You can combine that in the query against tmp_relevance to generate
scores for near matches and not just exact partial matches.

Does this give you some ideas to build on?
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Percent of match in condition

Maybe you could do something like the following with user variables (or
prepared statements):

set @sex =3D 1, @country =3D 120, @education =3D 0;

select if(sex_id =3D @sex, 1, 0) + if(country_id =3D @country, 1, 0) +
if(education_id =3D @education, 1, 0) as num_matches, sex_id, =
country_id,
education_id
from my_table
where sex_id =3D @sex
or country_id =3D @country
or education_id =3D @education
order by num_matches desc;

-Travis

-----Original Message-----
From: Ali A.F.N [mailto:aliooss@yahoo.com]=20
Sent: Thursday, October 21, 2010 7:58 AM
To: mysql@lists.mysql.com
Subject: Percent of match in condition

Hi All,

I have a table with different fileds and almost the type of all them are =

smallint.=A0I want to search on some fields with "OR" condition. I want =
to
know is=20
there possibility to know how many fileds matched exactly? then I can =
say
how=20
many percent match are available.

select * from my_table where sex_id = 1 or country_id =3D 120 or =
education_id

I=A0mean if in my table there are some records with=A0sex_id = 1 or =
country_id =3D

120=A0 then I got 2 (2 match) then I can say 66% percent match.

Thank you,

ali


=20

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dtravis_ard@hotmail.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg