Complex query question

Hi all,

I run MySQL 4.0.24 and I just ran into a problem that I am sure many
have solved before, so I thought I would ask. I apologize if this isn't
quite the right list, but I didn't really know where to send. Anyway,
say you have table products and table product variations. There can be 0
or many variations, and different products will have different
attributes (product car might have attribute hatchback=true and
attribute cylinders=3, whereas product car stereo may have attribute
watts=35x4 and DIN size=1).

I suppose we would have the tables:

product
------------
id
name
bla bla

attribute
-----------
item_id
name
value
bla bla


if we wanted to just display all the attributes for a given item (which
was the original intent :), this structure made a whole lot of sense.
However, now we are supposed to be able to search on attribute criteria.
Searching for radios with watts=35x4 is pretty easy and even searching
based on one attribute or the other makes sense, but how do I search for
a radio with 35x4 watts AND a DIN size of 1 since this would correspond
to one product record and two attributes records? Is there a way to do
so, or do I need to just assign different attributes tables to different
types of products (car_attribues vs car_stereo_attribues)? The reason
I'd rather not do this is that some of my attributes have like 50
records for the same product, and that would get beastly to manage.
Anyway, thanks for the help!

Corey



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Complex query question

Never mind, I figured it out. You do something like

select distinct item.id, name from
product inner join attributes as i1 on (i1.item_id=item.id and
i1.name='DIN size' and i1.value='1')
inner join attributes as i2 on (i2.item_id=i1.item_id and
i2.name='watts' and i2.value='35x4');

and presumable so on. Now I suppose I'll see how fast I can make it (my
guess is not very) :)

Thanks all
Corey

Corey Tisdale wrote:

> Hi all,
>
> I run MySQL 4.0.24 and I just ran into a problem that I am sure many
> have solved before, so I thought I would ask. I apologize if this
> isn't quite the right list, but I didn't really know where to send.
> Anyway, say you have table products and table product variations.
> There can be 0 or many variations, and different products will have
> different attributes (product car might have attribute hatchback=true
> and attribute cylinders=3, whereas product car stereo may have
> attribute watts=35x4 and DIN size=1).
>
> I suppose we would have the tables:
>
> product
> ------------
> id
> name
> bla bla
>
> attribute
> -----------
> item_id
> name
> value
> bla bla
>
>
> if we wanted to just display all the attributes for a given item
> (which was the original intent :), this structure made a whole lot of
> sense. However, now we are supposed to be able to search on attribute
> criteria. Searching for radios with watts=35x4 is pretty easy and even
> searching based on one attribute or the other makes sense, but how do
> I search for a radio with 35x4 watts AND a DIN size of 1 since this
> would correspond to one product record and two attributes records? Is
> there a way to do so, or do I need to just assign different attributes
> tables to different types of products (car_attribues vs
> car_stereo_attribues)? The reason I'd rather not do this is that some
> of my attributes have like 50 records for the same product, and that
> would get beastly to manage. Anyway, thanks for the help!
>
> Corey
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Complex query question

Just in case anyone was following this and wondering about speed, I
figured out a pretty good way to get around the speed issues. Since I
was caching search results in a seperate table anyway

search_cache
----------
item_id
searchterm_md5


the query can stay fast by doing the following:
append the attribute names and values for the purpose of getting a
different md5 fingerprint only
AND
always alphabetically order the attribute names and values

so, when someone searches for

stereo

cache all search results for stereo under md5('stereo')

then when someone narrows this search by watts, pull the original search
results from md5(stereo), do one inner join to the attributes table, and
then cache results under md5('stereo + watts=35x4'). as long as your
user interface only lets one attribute/value pair to be entered at a
time, you will wind up with a bigger but faster search cache thatI think
is a good compromise.

:)

Corey




Corey Tisdale wrote:

> Never mind, I figured it out. You do something like
>
> select distinct item.id, name from
> product inner join attributes as i1 on (i1.item_id=item.id and
> i1.name='DIN size' and i1.value='1')
> inner join attributes as i2 on (i2.item_id=i1.item_id and
> i2.name='watts' and i2.value='35x4');
>
> and presumable so on. Now I suppose I'll see how fast I can make it
> (my guess is not very) :)
>
> Thanks all
> Corey
>
> Corey Tisdale wrote:
>
>> Hi all,
>>
>> I run MySQL 4.0.24 and I just ran into a problem that I am sure many
>> have solved before, so I thought I would ask. I apologize if this
>> isn't quite the right list, but I didn't really know where to send.
>> Anyway, say you have table products and table product variations.
>> There can be 0 or many variations, and different products will have
>> different attributes (product car might have attribute hatchback=true
>> and attribute cylinders=3, whereas product car stereo may have
>> attribute watts=35x4 and DIN size=1).
>>
>> I suppose we would have the tables:
>>
>> product
>> ------------
>> id
>> name
>> bla bla
>>
>> attribute
>> -----------
>> item_id
>> name
>> value
>> bla bla
>>
>>
>> if we wanted to just display all the attributes for a given item
>> (which was the original intent :), this structure made a whole lot of
>> sense. However, now we are supposed to be able to search on attribute
>> criteria. Searching for radios with watts=35x4 is pretty easy and
>> even searching based on one attribute or the other makes sense, but
>> how do I search for a radio with 35x4 watts AND a DIN size of 1 since
>> this would correspond to one product record and two attributes
>> records? Is there a way to do so, or do I need to just assign
>> different attributes tables to different types of products
>> (car_attribues vs car_stereo_attribues)? The reason I'd rather not do
>> this is that some of my attributes have like 50 records for the same
>> product, and that would get beastly to manage. Anyway, thanks for the
>> help!
>>
>> Corey
>>
>>
>>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Complex query question

Have you considered using the MySql query cache feature?

-----Original Message-----
From: corey@bbqguys.com [mailto:corey@bbqguys.com]
Sent: 05 April 2005 04:37
To: corey@bbqguys.com
Cc: win32@lists.mysql.com
Subject: Re: Complex query question

Just in case anyone was following this and wondering about speed, I
figured out a pretty good way to get around the speed issues. Since I was
caching search results in a seperate table anyway

search_cache
----------
item_id
searchterm_md5


the query can stay fast by doing the following:
append the attribute names and values for the purpose of getting a
different md5 fingerprint only AND always alphabetically order the
attribute names and values

so, when someone searches for

stereo

cache all search results for stereo under md5('stereo')

then when someone narrows this search by watts, pull the original search
results from md5(stereo), do one inner join to the attributes table, and
then cache results under md5('stereo + watts=35x4'). as long as your user
interface only lets one attribute/value pair to be entered at a time, you
will wind up with a bigger but faster search cache thatI think is a good
compromise.

:)

Corey




Corey Tisdale wrote:

> Never mind, I figured it out. You do something like
>
> select distinct item.id, name from
> product inner join attributes as i1 on (i1.item_id=item.id and
> i1.name='DIN size' and i1.value='1') inner join attributes as i2 on
> (i2.item_id=i1.item_id and i2.name='watts' and i2.value='35x4');
>
> and presumable so on. Now I suppose I'll see how fast I can make it
> (my guess is not very) :)
>
> Thanks all
> Corey
>
> Corey Tisdale wrote:
>
>> Hi all,
>>
>> I run MySQL 4.0.24 and I just ran into a problem that I am sure many
>> have solved before, so I thought I would ask. I apologize if this
>> isn't quite the right list, but I didn't really know where to send.
>> Anyway, say you have table products and table product variations.
>> There can be 0 or many variations, and different products will have
>> different attributes (product car might have attribute hatchback=true
>> and attribute cylinders=3, whereas product car stereo may have
>> attribute watts=35x4 and DIN size=1).
>>
>> I suppose we would have the tables:
>>
>> product
>> ------------
>> id
>> name
>> bla bla
>>
>> attribute
>> -----------
>> item_id
>> name
>> value
>> bla bla
>>
>>
>> if we wanted to just display all the attributes for a given item
>> (which was the original intent :), this structure made a whole lot of
>> sense. However, now we are supposed to be able to search on attribute
>> criteria. Searching for radios with watts=35x4 is pretty easy and
>> even searching based on one attribute or the other makes sense, but
>> how do I search for a radio with 35x4 watts AND a DIN size of 1 since
>> this would correspond to one product record and two attributes
>> records? Is there a way to do so, or do I need to just assign
>> different attributes tables to different types of products
>> (car_attribues vs car_stereo_attribues)? The reason I'd rather not do
>> this is that some of my attributes have like 50 records for the same
>> product, and that would get beastly to manage. Anyway, thanks for the
>> help!
>>
>> Corey
>>
>>
>>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=tim.lloyd@biomni.com


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Hosting Mysql 5 / PHP5

Hi

Anyone recommend 3rd party hosting with Mysql 4.1.? and PHP 5 at reasonable
prices. I will be using a .co.uk domain.

Everyone is using 4.0.18 that I can see.

Gareth



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Complex query question

--=_alternative 005F54EA85256FDA_=
Content-Type: text/plain; charset="US-ASCII"

Corey Tisdale wrote on 04/04/2005 05:52:14 PM:

> Never mind, I figured it out. You do something like
>
> select distinct item.id, name from
> product inner join attributes as i1 on (i1.item_id=item.id and
> i1.name='DIN size' and i1.value='1')
> inner join attributes as i2 on (i2.item_id=i1.item_id and
> i2.name='watts' and i2.value='35x4');
>
> and presumable so on. Now I suppose I'll see how fast I can make it (my
> guess is not very) :)
>
> Thanks all
> Corey
>
> Corey Tisdale wrote:
>
> > Hi all,
> >
> > I run MySQL 4.0.24 and I just ran into a problem that I am sure many
> > have solved before, so I thought I would ask. I apologize if this
> > isn't quite the right list, but I didn't really know where to send.
> > Anyway, say you have table products and table product variations.
> > There can be 0 or many variations, and different products will have
> > different attributes (product car might have attribute hatchback=true
> > and attribute cylinders=3, whereas product car stereo may have
> > attribute watts=35x4 and DIN size=1).
> >
> > I suppose we would have the tables:
> >
> > product
> > ------------
> > id
> > name
> > bla bla
> >
> > attribute
> > -----------
> > item_id
> > name
> > value
> > bla bla
> >
> >
> > if we wanted to just display all the attributes for a given item
> > (which was the original intent :), this structure made a whole lot of
> > sense. However, now we are supposed to be able to search on attribute
> > criteria. Searching for radios with watts=35x4 is pretty easy and even

> > searching based on one attribute or the other makes sense, but how do
> > I search for a radio with 35x4 watts AND a DIN size of 1 since this
> > would correspond to one product record and two attributes records? Is
> > there a way to do so, or do I need to just assign different attributes

> > tables to different types of products (car_attribues vs
> > car_stereo_attribues)? The reason I'd rather not do this is that some
> > of my attributes have like 50 records for the same product, and that
> > would get beastly to manage. Anyway, thanks for the help!
> >
> > Corey
> >
> >
> >
>
>
As you discovered, your solution works but isn't completely scalable...
What you would like to do is to locate items based on how many parameters
they match, right. An exact match meets all requirements, a near match may
only be 3 out of 4, etc...

This has been asked and answered before (search the archives at
lists.mysql.com) so I will paraphrase the techniques described (not my
idea but it's a good one).

Here is the basic query

SELECT item_id, count(1) as matchcount
FROM attribute
WHERE (name='attr1' and value='value1')
OR (name='attr2' and value='value2')
OR (name='attr3' and value='value3')
OR (name='attr4' and value='value4')
GROUP BY item_id

Now an exact match would result in 4 for matchcount so test your results
for 4 values

SELECT ...
....
HAVING matchount=4;

But let's say you only want the top 10 matching items ranked according to
how many matches they made. You would use something like this

SELECT ...
....
ORDER BY matchcount desc
LIMIT 10;

OR to limit the results to just 10 exact matches (if that many exist)

SELECT ...
....
HAVING matchcount=4;
LIMIT 10;

What if you wanted to get fancy and give weight to each search term
(certain items were more important than the others.) This will give the
top 10 responses according to the weighted results

SELECT item_id
, SUM(CASE name
WHEN 'attr1' then weight1
WHEN 'attr2' then weight2
WHEN 'attr3' then weight3
WHEN 'attr4' then weight4
ELSE 0
END
) matchweight
FROM ...
....
ORDER BY matchweight DESC
LIMIT 10;

See! Your table design is very flexible and appropriate for what you want
to do. Sure it takes a little bit of manipulation to build your dynamic
SQL statements but because they (the queries) are all "pattern-based" you
can automate their production rather easily.

You may have noticed that I only pulled the item_id in each query. That's
because I would use those queries just to build a temporary table of the
item ids that match your query critera then JOIN your other tables to the
temporary table to fill in the specifics (like name, price, color, etc.).

Did this help you over the hump?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



--=_alternative 005F54EA85256FDA_=--

Re: Complex query question

Shawn,

SUM(CASE name
WHEN 'attr1' then weight1
WHEN 'attr2' then weight2
ELSE 0
END
) matchweight

Makes the magic. I had a sneaking suspicion that someone else had asked
before, I just couldn't find anything when I searched. The count and
having clauses and the case statement are exactly what I needed. Thanks!

Corey

SGreen@unimin.com wrote:

>
>
> Corey Tisdale wrote on 04/04/2005 05:52:14 PM:
>
> > Never mind, I figured it out. You do something like
> >
> > select distinct item.id, name from
> > product inner join attributes as i1 on (i1.item_id=item.id and
> > i1.name='DIN size' and i1.value='1')
> > inner join attributes as i2 on (i2.item_id=i1.item_id and
> > i2.name='watts' and i2.value='35x4');
> >
> > and presumable so on. Now I suppose I'll see how fast I can make it (my
> > guess is not very) :)
> >
> > Thanks all
> > Corey
> >
> > Corey Tisdale wrote:
> >
> > > Hi all,
> > >
> > > I run MySQL 4.0.24 and I just ran into a problem that I am sure many
> > > have solved before, so I thought I would ask. I apologize if this
> > > isn't quite the right list, but I didn't really know where to send.
> > > Anyway, say you have table products and table product variations.
> > > There can be 0 or many variations, and different products will have
> > > different attributes (product car might have attribute hatchback=true
> > > and attribute cylinders=3, whereas product car stereo may have
> > > attribute watts=35x4 and DIN size=1).
> > >
> > > I suppose we would have the tables:
> > >
> > > product
> > > ------------
> > > id
> > > name
> > > bla bla
> > >
> > > attribute
> > > -----------
> > > item_id
> > > name
> > > value
> > > bla bla
> > >
> > >
> > > if we wanted to just display all the attributes for a given item
> > > (which was the original intent :), this structure made a whole lot of
> > > sense. However, now we are supposed to be able to search on attribute
> > > criteria. Searching for radios with watts=35x4 is pretty easy and
> even
> > > searching based on one attribute or the other makes sense, but how do
> > > I search for a radio with 35x4 watts AND a DIN size of 1 since this
> > > would correspond to one product record and two attributes records? Is
> > > there a way to do so, or do I need to just assign different
> attributes
> > > tables to different types of products (car_attribues vs
> > > car_stereo_attribues)? The reason I'd rather not do this is that some
> > > of my attributes have like 50 records for the same product, and that
> > > would get beastly to manage. Anyway, thanks for the help!
> > >
> > > Corey
> > >
> > >
> > >
> >
> >
> As you discovered, your solution works but isn't completely
> scalable... What you would like to do is to locate items based on how
> many parameters they match, right. An exact match meets all
> requirements, a near match may only be 3 out of 4, etc...
>
> This has been asked and answered before (search the archives at
> lists.mysql.com) so I will paraphrase the techniques described (not my
> idea but it's a good one).
>
> Here is the basic query
>
> SELECT item_id, count(1) as matchcount
> FROM attribute
> WHERE (name='attr1' and value='value1')
> OR (name='attr2' and value='value2')
> OR (name='attr3' and value='value3')
> OR (name='attr4' and value='value4')
> GROUP BY item_id
>
> Now an exact match would result in 4 for matchcount so test your
> results for 4 values
>
> SELECT ...
> ...
> HAVING matchount=4;
>
> But let's say you only want the top 10 matching items ranked according
> to how many matches they made. You would use something like this
>
> SELECT ...
> ...
> ORDER BY matchcount desc
> LIMIT 10;
>
> OR to limit the results to just 10 exact matches (if that many exist)
>
> SELECT ...
> ...
> HAVING matchcount=4;
> LIMIT 10;
>
> What if you wanted to get fancy and give weight to each search term
> (certain items were more important than the others.) This will give
> the top 10 responses according to the weighted results
>
> SELECT item_id
> , SUM(CASE name
> WHEN 'attr1' then weight1
> WHEN 'attr2' then weight2
> WHEN 'attr3' then weight3
> WHEN 'attr4' then weight4
> ELSE 0
> END
> ) matchweight
> FROM ...
> ...
> ORDER BY matchweight DESC
> LIMIT 10;
>
> See! Your table design is very flexible and appropriate for what you
> want to do. Sure it takes a little bit of manipulation to build your
> dynamic SQL statements but because they (the queries) are all
> "pattern-based" you can automate their production rather easily.
>
> You may have noticed that I only pulled the item_id in each query.
> That's because I would use those queries just to build a temporary
> table of the item ids that match your query critera then JOIN your
> other tables to the temporary table to fill in the specifics (like
> name, price, color, etc.).
>
> Did this help you over the hump?
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org