Received: from socrates.catalyst.net.nz (socrates.catalyst.net.nz [203.96.153.226]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBBMNLm30342 for ; Tue, 11 Dec 2001 17:23:21 -0500 (EST) (envelope-from andrew@catalyst.net.nz) Received: from heidegger.catalyst.net.nz (hume.catalyst.net.nz [203.96.153.228]) by socrates.catalyst.net.nz (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id LAA12793; Wed, 12 Dec 2001 11:23:01 +1300 X-Authentication-Warning: socrates.catalyst.net.nz: Host hume.catalyst.net.nz [203.96.153.228] claimed to be heidegger.catalyst.net.nz Received: from 127.0.0.1 (ident=unknown) by heidegger.catalyst.net.nz with esmtp (MasqMail 0.1.15) id 16DvIf-1ql-00; Wed, 12 Dec 2001 11:22:57 +1300 Subject: Re: How to manage search results? From: Andrew McMillan To: Marius Andreiana Cc: pgsql-php In-Reply-To: <1007986746.8625.0.camel@aurora.wdg.ro> References: <1007976478.8206.2.camel@aurora.wdg.ro> <1007978396.2311.6.camel@kant.mcmillan.net.nz> <1007980445.8519.0.camel@aurora.wdg.ro> <1007982394.2311.10.camel@kant.mcmillan.net.nz> <1007986746.8625.0.camel@aurora.wdg.ro> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Evolution/1.0 (Preview Release) Date: 12 Dec 2001 11:22:56 +1300 Message-Id: <1008109377.999.27.camel@kant.mcmillan.net.nz> Mime-Version: 1.0 X-Archive-Number: 200112/10 X-Sequence-Number: 785 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