Complicated query... is there a simpler way?
I've got a nasty looking search query, and I'm afraid with a large table
it's going to be horribly inefficient, and I was wondering if anybody
could think of a way to slim this one down.
I'll simplify my situation as much as I can.
I'm working with two tables here. The first is a user table:
create table users(
userid integer not null primary key,
firstnames text not null,
lastname text not null
);
The second is a message board table:
create table posts(
postid integer not null primary key,
reply_to integer references posts(postid),
author integer not null references users(userid),
subject text not null,
body text not null
);
I think the only columns which need explaining are postid and reply_to.
When a new thread is created, a row is inserted into posts with a unique
postid and a null reply_to. Any subsequent responses to this original
post are also given unique postid's but their reply_to field references
the start of the thread.
I'm writing a search function. It would be useful to search by thread,
and not by individual post. So I've created an aggregate function
concat(text). I've also created a scoring function that takes in three
text variables. The first should be the query (which I'll represent as
$query, since it's provided by the webserver). The second should be the
subject to search (subject is scored differently than the body) and the
third should be the body to search.
Here's the query:
select lastname, firstnames, subject, threadid from posts, users,
(select threadid, concat(body) as thread_body from (select postid as
threadid, body from posts where reply_to is null union select reply_to
as threadid, body from posts where reply_to is not null) as
inner_subquery group by threadid) as outer_subquery where users.userid =
posts.author and threadid = postid and
score_search('$query',subject,thread_body) > 0 order by
score_search('$query',subject,thread_body);
Forgive me if I have any syntactic errors. I'm translating from my real
query. My real query's a little larger since my actualy scenario's a
little more complicated. But that's four actual select calls to produce
this. Is there a prettier way anybody can think of?
Thanks!
-jag
Joshua Adam Ginsberg wrote:
I've got a nasty looking search query, and I'm afraid with a large table
it's going to be horribly inefficient, and I was wondering if anybody
could think of a way to slim this one down.
Here's the query:
select lastname, firstnames, subject, threadid from posts, users,
(select threadid, concat(body) as thread_body from (select postid as
threadid, body from posts where reply_to is null union select reply_to
as threadid, body from posts where reply_to is not null) as
inner_subquery group by threadid) as outer_subquery where users.userid =
posts.author and threadid = postid and
score_search('$query',subject,thread_body) > 0 order by
score_search('$query',subject,thread_body);
Just thinking out loud, and it depends on how you are doing your
scoring, but what about scoring each message individually and the
grouping by threadid summing the scores? Should bring it down to two
levels plus a join with users.
- Richard Huxton
Hello!
What SQL query will help me to concatenate two different tables
with different number of rows? For example , i have first table
with column1 and column2 , having 3 rows , and second table
with column3, column4 , having 5 rows. How to make
third table with column1,column2,column3,column4 and 5 rows in it
(and last two rows in column1 and column2 are empty)
Thanks for sugestions
Igor.
From: "Igor" <dbmanager@osb368.nnov.ru>
Hello!
What SQL query will help me to concatenate two different tables
with different number of rows? For example , i have first table
with column1 and column2 , having 3 rows , and second table
with column3, column4 , having 5 rows. How to make
third table with column1,column2,column3,column4 and 5 rows in it
(and last two rows in column1 and column2 are empty)
Something along the lines of:
SELECT col1,col2,'' as dummy3, '' as dummy4
FROM table1
UNION
SELECT '' as dummy1, '' as dummy2, col3, col4
FROM table2
should do it for you (not tested)
- Richard Huxton
The problem, of course, is your database schema.
SQL really sucks for processing of recursive queries, you should reference
beginning of the thread in your posts table, and search by that.
On Wed, 27 Jun 2001, Joshua Adam Ginsberg wrote:
Show quoted text
I've got a nasty looking search query, and I'm afraid with a large table
it's going to be horribly inefficient, and I was wondering if anybody
could think of a way to slim this one down.I'll simplify my situation as much as I can.
I'm working with two tables here. The first is a user table:
create table users(
userid integer not null primary key,
firstnames text not null,
lastname text not null
);The second is a message board table:
create table posts(
postid integer not null primary key,
reply_to integer references posts(postid),
author integer not null references users(userid),
subject text not null,
body text not null
);I think the only columns which need explaining are postid and reply_to.
When a new thread is created, a row is inserted into posts with a unique
postid and a null reply_to. Any subsequent responses to this original
post are also given unique postid's but their reply_to field references
the start of the thread.I'm writing a search function. It would be useful to search by thread,
and not by individual post. So I've created an aggregate function
concat(text). I've also created a scoring function that takes in three
text variables. The first should be the query (which I'll represent as
$query, since it's provided by the webserver). The second should be the
subject to search (subject is scored differently than the body) and the
third should be the body to search.Here's the query:
select lastname, firstnames, subject, threadid from posts, users,
(select threadid, concat(body) as thread_body from (select postid as
threadid, body from posts where reply_to is null union select reply_to
as threadid, body from posts where reply_to is not null) as
inner_subquery group by threadid) as outer_subquery where users.userid =
posts.author and threadid = postid and
score_search('$query',subject,thread_body) > 0 order by
score_search('$query',subject,thread_body);Forgive me if I have any syntactic errors. I'm translating from my real
query. My real query's a little larger since my actualy scenario's a
little more complicated. But that's four actual select calls to produce
this. Is there a prettier way anybody can think of?Thanks!
-jag
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Thank you for replay, it is working, but
i would like to get result table with 5 rows.
In your example we willl get 8 rows
RH> From: "Igor" <dbmanager@osb368.nnov.ru>
Hello!
What SQL query will help me to concatenate two different tables
with different number of rows? For example , i have first table
with column1 and column2 , having 3 rows , and second table
with column3, column4 , having 5 rows. How to make
third table with column1,column2,column3,column4 and 5 rows in it
(and last two rows in column1 and column2 are empty)
RH> Something along the lines of:
RH> SELECT col1,col2,'' as dummy3, '' as dummy4
RH> FROM table1
RH> UNION
RH> SELECT '' as dummy1, '' as dummy2, col3, col4
RH> FROM table2
On Wed, Jun 27, 2001 at 02:25:23PM +0400, Igor wrote:
Thank you for replay, it is working, but
i would like to get result table with 5 rows.
In your example we willl get 8 rows
I think it's time to reexamine what you're trying to acheive. What you are
asking is not possible with SQL, at least not easily. Do it in your client
code.
RH> From: "Igor" <dbmanager@osb368.nnov.ru>
Hello!
What SQL query will help me to concatenate two different tables
with different number of rows? For example , i have first table
with column1 and column2 , having 3 rows , and second table
with column3, column4 , having 5 rows. How to make
third table with column1,column2,column3,column4 and 5 rows in it
(and last two rows in column1 and column2 are empty)RH> Something along the lines of:
RH> SELECT col1,col2,'' as dummy3, '' as dummy4
RH> FROM table1
RH> UNION
RH> SELECT '' as dummy1, '' as dummy2, col3, col4
RH> FROM table2
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
- Artificial Intelligence is the science of making computers that behave
- like the ones in the movies.