How does a MySQL "AND" work? (sub-searches)?

[binary-nomad]

Hello,
When I do a "AND" in a SQL query, eg. SELECT NAME WHERE SEX="male" AND
AGE= "30", MySQL does a sub-search, right? i.e. it does the "WHERE
SEX=male" first, and then searches through the list of *those* results
to see if AGE=30?
Does it do this from left to right? i.e. in the query above, would the
table always, and necessarily, get searched for "SEX=male" first, and
"AGE=30" second?
Thanks very much.

[Chris Hope]

bi**********@hotmail.com wrote:

Hello,
When I do a "AND" in a SQL query, eg. SELECT NAME WHERE SEX="male" AND
AGE= "30", MySQL does a sub-search, right? i.e. it does the "WHERE
SEX=male" first, and then searches through the list of *those* results
to see if AGE=30?
Does it do this from left to right? i.e. in the query above, would the
table always, and necessarily, get searched for "SEX=male" first, and
"AGE=30" second?

If 'age' had an index on it and 'sex' didn't then it would most likely
search 'age' first. If they both had indexes and one of them had more
different values than the other (eg 'age' in your example) then that
one would probably be seached on first. If neither had indexes then I
would assume it would be in the order you specify, but at the end of
the day if you don't index either column does it really matter what the
DBMS does?

If you're wanting to find out what sort of stuff is being searched on
when doing a query, do "explain ..." (eg explain select foo from bar
where ...)

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

[binary-nomad]

Uhh yeah, it does matter, for what I'm trying to implement, otherwise I
wouldn't have asked!.... :)

"Probably" doesn't do me any good in both cases - I need to know -how
MySQL works- internally.

Posted to mysql-internals after reading this </self-thwap!>

Thanks for your reply tho. :)

[Malcolm Dew-Jones]

bi**********@hotmail.com wrote:
: Uhh yeah, it does matter, for what I'm trying to implement, otherwise I
: wouldn't have asked!.... :)

Do you have an example where it would matter?

[binary-nomad]

Malcolm Dew-Jones wrote:

bi**********@hotmail.com wrote:
: Uhh yeah, it does matter, for what I'm trying to implement, otherwise I
: wouldn't have asked!.... :)

Do you have an example where it would matter?

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).

[Gordon Burditt]

>> : Uhh yeah, it does matter, for what I'm trying to implement, otherwise I

: wouldn't have asked!.... :)

Do you have an example where it would matter?
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,

If it's got an index on "age", and age = 30 yields fewer records
than the index on sex = 'Male', it makes sense for MySQL to start
looking at records with age = 30 using the index and then check the
other criteria as it fetches the records.

If there is no index at all (on a simple query with no joins), it's
going to have to scan all the records once. It will NOT do one
scan for 'Male', then scan those records for '30', then scan those
records for 'Tennis'. It checks each record for all the criteria.

otherwise it would be better to use some other method altogether (not a
SQL db at all).

Gordon L. Burditt

[Malcolm Dew-Jones]

bi**********@hotmail.com wrote:

: Malcolm Dew-Jones wrote:
: > bi**********@hotmail.com wrote:
: > : Uhh yeah, it does matter, for what I'm trying to implement, otherwise I
: > : wouldn't have asked!.... :)
: >
: > Do you have an example where it would matter?

: 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).

That sounds like a 100% generic usage of a relational database - exactly
the sort of thing mysql is designed to do. I think your concern is
unfounded.

You would be better off learning the various capabilities of mysql rather
than programming your own db software replacement in an attempt to
optimize this query.

--

This programmer available for rent.

[www.1-script.com]

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!
##-----------------------------------------------##