Обсуждение: How to manage search results?

Hi

I was wondering how others manage search results.

We do it now like this:
each user has a predefined table for holding the results (a list of
IDs). When user is created/deleted,
this table (query_results_$user_id) is created/deleted too. If this
table is deleted by mistake (by hand),
one cannot perform a search anymore.

problems:
* you have to remember to create/delete table each time user is
created/deleted (not too hard)
using a temporary table with aa unique name determined at runtime would
be better
* If there are more types of searches you run into problems.
results from deleted members are hold in the same table, so
when you go to search screen for normal members you'll
see there are x results.

Proposal so far: when going to search page check if there's a temporary
table name set to be used
for holding results for that search type (members, deleted_members, ...)
If not, determine a name (random, check if it already exists in db),
keep it in a session variable and create that table.

Any other ideas?

Thanks!
--
Marius Andreiana
Web Development Group, Inc.

On Mon, 2001-12-10 at 22:27, Marius Andreiana wrote:
>
> I was wondering how others manage search results.

I just construct my query in the normal way and append a LIMIT ...
OFFSET ... clause to control the finally select records.

Although this gets slower as the user pages and pages further through
the possibilities.  I generally find that this is self-managing though
and the user issues a different search.

Have to watch for search engines though - they are, unfortunately,
indefatigable.  If I recognise a search engine I don't provide the back
/ forward links...

For tables of a reasonable size (up to maybe 10,000 records) there is no
problem anyway, beyond that strange performance holes happen when
PostgreSQL doesn't always switch query plans at the right place...

Cheers,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267

On Lu, 2001-12-10 at 11:59, Andrew McMillan wrote:
> I just construct my query in the normal way and append a LIMIT ...
> OFFSET ... clause to control the finally select records.
I can't do that.

Some of the queries I use are complex; after you run a query you
can refine it, add to results, substract from results... I use
a table to build the results from initial query, then add/remove to it
as needed until search is done and finally user starts to
browse the results. I can't make it in one huge query.

For simple queries your solution is the best, no need for
a table; I assume you just save the query in a session variable.
I also assume your query doesn't take more than a second to run.

thanks
--
Marius Andreiana
--
You don't have to go to jail for helping your neighbour
http://www.gnu.org/philosophy/

On Mon, 2001-12-10 at 23:32, Marius Andreiana wrote:
> On Lu, 2001-12-10 at 11:59, Andrew McMillan wrote:
> > I just construct my query in the normal way and append a LIMIT ...
> > OFFSET ... clause to control the finally select records.
> I can't do that.
>
> Some of the queries I use are complex; after you run a query you
> can refine it, add to results, substract from results... I use
> a table to build the results from initial query, then add/remove to it
> as needed until search is done and finally user starts to
> browse the results. I can't make it in one huge query.
>
> For simple queries your solution is the best, no need for
> a table; I assume you just save the query in a session variable.
> I also assume your query doesn't take more than a second to run.

No, the users can save query criteria and so forth, and queries can take
a minute or two to run.

The underlying data being queried changes a fair bit.  This is a news
database, so the users want their queries to stay constant, but the
results to vary.  For complex (5 or more boolean related keywords)
criteria they don't care so much about the minute, it's the results that
they want.

Perhaps you need to impart a broader understanding of what your
application is trying to achieve.  I assume from your comments that the
data are fairly static, and that the users are analysing it in some way.

Cheers,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267

On Lu, 2001-12-10 at 13:06, Andrew McMillan wrote:
> > I also assume your query doesn't take more than a second to run.
>
> No, the users can save query criteria and so forth, and queries can take
> a minute or two to run.
so for adding/refining/substracting you use in one big query UNION,
INTERSECT, EXCEPT instead of a temporary table?

Is it ok with your users to run a search which takes say 30 secs,
see first 10 results, then wait again 30 secs to see the next 10 ?
(if you run the query again...); perhaps I misunderstood something

> The underlying data being queried changes a fair bit.  This is a news
> database, so the users want their queries to stay constant, but the
> results to vary.
yes, same here. We have members (about 13000) and staff performs
searches with criterias like state of licensure is xx or
number of comments > y. (The criterias expand as staff requests)
But the data queried doesn't change as often when just browsing
through search results, so I'd rather keep a list of IDs in a
temporary table than run the query every time they want to see
next page.

Once one has these results they can go to the individual view of
a member and change some data then return to browsing results,
or mass-mail everybody in search results. Again, in the mass mail
script is faster to start sending mail right away than perform
the query (queries) again and then start with the mail.

thanks
--
Marius Andreiana
--
You don't have to go to jail for helping your neighbour
http://www.gnu.org/philosophy/

On Tue, 2001-12-11 at 01:19, Marius Andreiana wrote:
> >
> > No, the users can save query criteria and so forth, and queries can take
> > a minute or two to run.
> so for adding/refining/substracting you use in one big query UNION,
> INTERSECT, EXCEPT instead of a temporary table?

Yes, except it is just an n-way join, where n is related to the number
of search terms they are using.


> Is it ok with your users to run a search which takes say 30 secs,
> see first 10 results, then wait again 30 secs to see the next 10 ?
> (if you run the query again...); perhaps I misunderstood something

Yes, it is.  In most cases they will get a sub 5-second response anyway,
and only in rare cases do they actually want to look beyond the first
page.


> > The underlying data being queried changes a fair bit.  This is a news
> > database, so the users want their queries to stay constant, but the
> > results to vary.
> yes, same here. We have members (about 13000) and staff performs
> searches with criterias like state of licensure is xx or
> number of comments > y. (The criterias expand as staff requests)
> But the data queried doesn't change as often when just browsing
> through search results, so I'd rather keep a list of IDs in a
> temporary table than run the query every time they want to see
> next page.

Well, could you tie the temp table to their session?  That might make it
easier to go through and clean up the temp table later.


> Once one has these results they can go to the individual view of
> a member and change some data then return to browsing results,
> or mass-mail everybody in search results. Again, in the mass mail
> script is faster to start sending mail right away than perform
> the query (queries) again and then start with the mail.

I've never gone for that 'browse and edit results' UI concept. I just
produce a list with a bunch of URL's linking to the maintenance page
using target=_new - let them close the window when they have finished.

I sort of see where you are coming from though.

Possibly you could just store an array of ID #'s in one row of a table,
and then have one table with all of your sort results in it.  If you are
paging through them then a query in a loop through entries in that array
should be very quick.

Using this approach you could also invalidate saved searches quite
easily in appropriate situations.

Regards,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267