bi**********@hotmail.com wrote:
If I'm searching for say, "Males" who are "30" who
wear "Boxers", or
whose favourite sport is "Tennis", it would be very
reassuring to know
that by the time it gets to "Sport", all "Women"
have been left out of
the search, ie. it's not going through the whole table at that point,
otherwise it would be better to use some other method altogether (not a
SQL db at all).
As it was pointed out here couple times, MySQL will use indexes to perform
such search IF they exist. The thing is: you can easily create an index
yourself and thus steer MySQL, so to speak, to search exactly the way you
want it.
You can create indexes for several fields at ones, and that will be the
index that the system will use. Pay attention to the order of fields in
the index. In your example you want to include sport first because there
are less chances someone like a particular sport than to belong to a
particular sex (~50%), and therefore the system will search through less
record. Next include age for the same reason: less records will show up
for a particular age since you have more choices than just two. The last
table to include in this index will be sex. By the time the system will
get to searching for the sex, the record will already be narrowed down so
well by previous two searches (sport,age) that it will not matter if you
have too many matches.
The SQL code for creating such index will look like this:
CREATE INDEX `index_name` ON `table_name` (sport,age,sex);
Like I said, pay attention to the order of the fields as you could easily
increase time to search thru the database by a factor of 10 (or more!) by
picking a wrong order (sex,age,sport) although the end result will be the
same set of record.
Goof luck!
--
Cheers,
Dmitri
See Site Sig Below
-------------------------------------
--
##-----------------------------------------------##
Article posted with Web Developer's USENET Archive
http://www.1-script.com/forums
Web and RSS gateway to your favorite newsgroup -
mailing.database.mysql - 1596 messages and counting!
##-----------------------------------------------##