Hi,

If I have a simple 'user' table containing some basic information.

Something like "fname, lname, displayname, age, city, country, state"
and so forth.

And, I would like the visitors to be able to search by fname, lname,
displayname, age, city, country _or_ state

so my select would be "SELECT * FROM table_user WHERE fname='xyz'"

Must I create a separate index for each "fname, lname, displayname, age,
city, country, state, ..."?
Or would a single select be enough?

Would I need to do something different if my select was using a like,
("SELECT * FROM table_user WHERE fname LIKE '%xyz%'")?

Or do you think that the design itself should be different altogether?

Many thanks

Simon

On 10/19/2011 5:30 AM, Simon wrote:
RDBMS's can only use an index for a single column search if it's the
first column in the index. For instance, if you had an index on "lname,
fname", the RDBMS can use it for a search on lname, but not for one on
fname.

This will almost assuredly require a table scan.

Think of how indexes work. The index is basically a sorted list of data
for the columns specified. This allows the RDBMS to perform a binary
search of the index to find the requested row(s).

But the sort is in the order of the column specification. In the
example above, could have the following index:

anderson, mark
doe, john
smith, don

The index could be used for a search by lname, but not fname.

Likewise, a search on "lname like '%it%'" could not use the index
(although a search on "lname like 'sm%'" could).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2011/10/19 12:33 PM, Jerry Stuckle wrote:
Thanks for the reply and explanation.

It would then make sense to create an index for each and every possible
select, (and also to _not_ use LIKE '%...%').

In my case, that would mean 13 new indexes.

Do you think that the design is flawed in the first place?
Should that information be in a different table structure?

Thanks

Simon

On Wed, 19 Oct 2011 13:17:49 +0200, Simon wrote:
It depends. Lots of index can be a drawback if you're updating or
inserting data, because each index may have to be updated as well. So,
inserting a lot of records may take 20 or 30 times as long to do as not
having those indexes.

However, lots of indexes may not be a big deal if you aren't inserting a
lot of data all the time. If you only insert 20 records a day, who gives
a crap if it takes 0.003 seconds or it takes 0.124 seconds per? On the
rare occasions when you DO have tens of thousands of records to put in,
you can unhook the indexes, load the data, then re-add the indexs and
they get built only once instead of getting adjusted for every one.

You might also consider what people are actually searching FOR. Are they
looking for states because they have legal or jurisdictional reasons to?
Are they looking for location data for mailing? Are they looking to find
out what's close by? If people mostly only care what's close, you may
want to rethink searching by state, city, and zipcode entirely and
switch to a spatial process instead: find a nice way to get coordinates
for addresses and store and index that instead of the legal names of
places.

--
32. I will not fly into a rage and kill a messenger who brings me bad
news just to illustrate how evil I really am. Good messengers are
hard to come by.
--Peter Anspach's list of things to do as an Evil Overlord

"Peter H. Coffin" <hellsop (AT) ninehells (DOT) com> wrote

As well as time to update those indexes (which as may or may not be a big
deal), be aware of the storage requirements for each index. With one index
per field the indexes will be taking up more space than the table data. This
is probably not a significant factor for a small database, but is worth
being aware of.

Brian Cryer
http://www.cryer.co.uk/brian