Need som help on a query

mrblom

I tried in an earlier post to explain a problem and i was helped a long way, but it stopped somewhere. So I'm starting over again. 🙂

This is my case...

I'm doing some research for differendt ways to make searches fast and efficient...

At the moment I'm looking at a concept I call "keyword search".

I have a table containing my data to be displayed. This table is pretty large, so its not efficient to search in this.

The concept of the table is like this:

Table: users
id | name1 | name2 | name3

to speed up the search i have made two more tables

Table: words
id | word

word is unique, so the table is very "searchable" cause th word "smith" will only give one hit.

And you probably guessed right.
the last table stores the relations between users and words.

Table: relation
uid | wid

The tables are of course indexed

I use a PostgesSQL database.

OK, here we go
The result is based on the hits we get in the words table.
So i have to go through the relation table to retrieve any relevant data.

query:
SELECT relation.uid, COUNT(relation.wid) AS matches
FROM relation, words WHERE relation.wid = words.id
AND ( words.word = 'foo' or words.word = 'bar' )
GROUP BY relation.uid ORDER BY matches DESC

This gives me all the records that matches my search. And even sorted by the ones with the most mathces. This is good and fast...

But no relevant data yet...

So i thought.. this cant be too hard... hehe

I use the query above as a subselect!!!

This is what I got.

SELECT * FROM users WHERE users.id IN (
SELECT relation.uid, COUNT(relation.wid) AS matches
FROM relation, words WHERE relation.wid = words.id
AND ( words.word = 'foo' or words.word = 'bar' )
GROUP BY relation.uid ORDER BY matches DESC
);

This is what I got
Error: Subselect has too many fields

I understand why I get the error, but I will need some help to make this work and be fast as ...

Thanks guys...

schnils

Try to avoid the subselect. You can also combine the two select-statements to one.

Nils

Wien

You want us to do all your job :rolleyes: I almost gave you the solution... What you need to do is join user with relation and forget Subselect, simply because you don't need it for what you want

sould be something like :

SELECT users.FieldsYouWant, COUNT(relation.wid) AS matches 
FROM relation, words, users 
WHERE users.id = relation.uid AND relation.wid = words.id 
AND ( words.word = 'foo' or words.word = 'bar' ) 
GROUP BY relation.uid ORDER BY matches DESC

Wien

Hum sorry my query is not correct since the relation is many to many... i'll be back, i'm in the middle of diong something right now

mrblom

That was my problem to...

I've tried different approaches, but I didn't get it to work real good. Anyone?

eomer

Try this one...

SELECT * FROM users WHERE users.id IN (
SELECT relation.uid
FROM relation, words WHERE relation.wid = words.id
AND ( words.word = 'foo' or words.word = 'bar' )
GROUP BY relation.uid ORDER BY COUNT(relation.wid) DESC
);

Basically if you you need to have the same number of columns in your query and subquery... but if you really need the count, you can put it in the order by clause, and it should not modify your results...

mrblom

Thanks...

I've tried it out and I think I'm pretty sure that a subselect is to heavy for this operation. So I've been playing around with a query and have somethin that executes fast, but looking at it makes me think its to heavy... It's actually an extension of the query suggested by "wien" earlier in the thread.

Query:

SELECT users.name1, users.name2, users.name3, realtion.uid, COUNT(relation.wid) AS matches
FROM relation, words, users
WHERE users.id = relation.uid AND relation.wid = words.id
AND (words.word = 'foo' or words.word = 'bar')
GROUP BY users.name1, users.name2, users.name3, relation.uid
ORDER BY matches DESC;

This query is pretty fast considering the amount of data, but do any of you guys see how i can tune it?