I was a little to quick with the send button.
Can you do a query like this:
(I know that the * syntax is not correct, but is there something
equivalent to it?
SELECT from cars
WHERE
make=5 AND
model=* AND
body_color=7 AND
tire_type = * AND
hub_caps_type = 1
If you could perform a query like the one above, would MySQL still
use the multi-column index that I set up?
-James
On Apr 24, 2007, at 4:47 PM, James Tu wrote:
What do you guys think of this approach...
Always query on all 5 columns...and then create a multicolumn index
using all 5 columns?
-James
On Apr 24, 2007, at 11:42 AM, James Tu wrote:
Thanks Mike.
So let's say I have in index on each of the columns below...and I
do a search for
make=5
model=2
body_color=7
tire_type=11
hub_caps_type=1
MySQL will only pick one of them right? Let's say it picks
make_index.
Then what does it do? Does it scan the entire set of results
returned by make=5 to match the other criteria?
-James
On Apr 23, 2007, at 5:49 PM, mos wrote:
James,
A lot depends on how many rows you are searching on. If
you only have a couple thousand rows, then a table scan will
still be fast. If you are searching more rows, say more than
10,000, then using the proper index will speed things up. Using a
compound index is only useful if the user is searching on at
least the first field of the index. For now, your best bet is to
build an index on each of the commonly searched columns and MySQL
will choose the best index for the search.
Mike
At 11:16 AM 4/23/2007, James Tu wrote:
I have a table which will be searched via some of the fields in the
column.
An example of the list of searcheable columns:
make
model
body_color
tire_type
hub_caps_type
The thing is that people might do a search using one or many of the
fields as criteria.
For example someone might search for :
body_color = 1 AND tire_type = 11
or just
model = 22
I read that MySQL only uses one index when it performs a query. I
did an EXPLAIN and it appears that only one of the indices is used.
What is the proper way to setup indices in this case?
Shoud I add an Index for each of these fields OR create a
multicolumn
index using all of these fields?
-James
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
