Received: from localhost (maia-4.hub.org [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id B92D69FC14E
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 25 May 2007 13:49:42 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 67416-03 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 25 May 2007 13:49:39 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from h07.llord.com (unknown [195.140.142.194])
	by postgresql.org (Postfix) with ESMTP id D5A319FB8DC
	for <pgsql-performance@postgresql.org>;
	Fri, 25 May 2007 13:49:38 -0300 (ADT)
Received: from par69-8-88-161-102-87.fbx.proxad.net ([88.161.102.87]
	helo=apollo13) by h07.llord.com with esmtpa (Exim 4.66)
	(envelope-from <lists@peufeu.com>)
	id 1Hrcxm-00007v-8Z; Fri, 25 May 2007 18:48:26 +0200
Date: Fri, 25 May 2007 18:51:04 +0200
To: "Richard Huxton" <dev@archonet.com>, mark@mark.mielke.cc
Subject: Re: LIKE search and performance
From: PFC <lists@peufeu.com>
Cc: "Mark Lewis" <mark.lewis@mir3.com>,
	"James Mansion" <james@mansionfamily.plus.com>,
	"Magnus Hagander" <magnus@hagander.net>,
	"Alexander Staubo" <alex@purefiction.net>, Andy <frum@ar-sd.net>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <009501c79d4c$445428f0$0b00a8c0@mpsro.dom>
	<88daf38c0705230905u1a981102k691b47bd36011a80@mail.gmail.com>
	<4655DE75.4080506@mansionfamily.plus.com>
	<4655E64B.5050503@hagander.net>
	<4655FB8A.1040707@mansionfamily.plus.com>
	<1180040560.31471.262.camel@archimedes>
	<20070524215450.GA3858@mark.mielke.cc>
	<46569AA5.10205@archonet.com>
	<20070525141630.GA12162@mark.mielke.cc>
	<4657023A.20209@archonet.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.tsv6fevgcigqcu@apollo13>
In-Reply-To: <4657023A.20209@archonet.com>
User-Agent: Opera Mail/9.10 (Linux)
X-PopBeforeSMTPSenders: junk@peufeu.com, lists@peufeu.com, peufeu@peufeu.com,
	pfcaillaud@peufeu.com
X-AntiAbuse: This header was added to track abuse,
	please include it with any abuse report
X-AntiAbuse: Primary Hostname - h07.llord.com
X-AntiAbuse: Original Domain - postgresql.org
X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12]
X-AntiAbuse: Sender Address Domain - peufeu.com
X-Source: 
X-Source-Args: 
X-Source-Dir: 
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=2.258 tagged_above=0 required=5 tests=AWL=-0.518,
	BAYES_50=0.001, RCVD_IN_NJABL_SPAM=2.775
X-Spam-Level: **
X-Archive-Number: 200705/521
X-Sequence-Number: 24911



> OK - any application that allows user-built queries: <choose column:  =

> foo> <choose filter: contains> <choose target: "bar">
>
> Want another? Any application that has a "search by name" box - users =
 =

> can (and do) put one letter in and hit enter.
>
> Unfortunately you don't always have control over the selectivity of  =

> queries issued.

	-*- HOW TO MAKE A SEARCH FORM -*-

	Imagine you have to code the search on IMDB.

	This is what a smart developer would do

	First, he uses AJAX autocompletion, so the thing is reactive.
	Then, he does not bother the user with a many-fields form. Instead of  =

forcing the user to think (users HATE that), he writes smart code.
	Does Google Maps have separate fields for country, city, street, zipcod=
e  =

? No. Because Google is about as smart as it gets.

	So, you parse the user query.

	If the user types, for instance, less than 3 letters (say, spi), he  =

probably wants stuff that *begins* with those letters. There is no point=
  =

in searching for the letter "a" in a million movie titles database.
	So, if the user types "spi", you display "name LIKE spi%", which is  =

indexed, very fast. And since you're smart, you use AJAX. And you displa=
y  =

only the most popular results (ie. most clicked on).

http://imdb.com/find?s=3Dall&q=3Dspi

	Since 99% of the time the user wanted "spiderman" or "spielberg", you'r=
e  =

done and he's happy. Users like being happy.
	If the user just types "a", you display the first 10 things that start =
 =

with "a", this is useless but the user will marvel at your AJAX skillz. =
 =

Then he will probably type in a few other letters.

	Then, if the user uses his space bar and types "spi 1980" you'll  =

recognize a year and display spielberg's movies in 1980.
	Converting your strings to phonetics is also a good idea since about 0.=
7%  =

of the l33T teenagers can spell stuff especially spiElberg.

	Only the guy who wants to know who had sex with marilyn monroe on the  =

17th day of the shooting of Basic Instinct will need to use the Advanced=
  =

search.

	If you detect several words, then switch to a prefix-based fulltext  =

search like Xapian which utterly rocks.
	Example : the user types "savin priv", you search for "savin*" NEAR  =

"priv*" and you display "saving private ryan" before he has even finishe=
d  =

typing the second word of his query. Users love that, they feel  =

understood, they will click on your ads and buy your products.

	In all cases, search results should be limited to less than 100 to be  =

easy on the database. The user doesn't care about a search returning mor=
e  =

than 10-20 results, he will just rephrase the query, and the time taken =
to  =

fetch those thousands of records with name LIKE '%a%' will have been  =

utterly lost. Who goes to page 2 in google results ?

	BOTTOM LINE : databases don't think, you do.