MySQL multiple indexes vs multi-column index for searching

In the software I'm writing one has the ability to search a given table for information. The search form has 5 fields, all which of course correspond to different columns in a table, but all of the fields are optional.

My question is in regard to whether or not multi-column indexing will work and the proper way to build a query for it.

If I have a single index across 5 columns, and I build a query to search them, when it comes to fields in this index I'm not searching, do I do something like:

field1 = 10 AND field2 > 0 AND ...

Or should I not include the unused columns at all?

I've searched around for information about multiple column indices, but I can't seem to find what to do when you need to skip one of the columns in a given index or if you simply don't care about that one in that specific instance.

-------------Problems Reply------------

You have to understand in MySQL (in the case of InnoDB) it only uses the left most prefix of your indexes. So if the index is in Field1 + Field2, querying with only Field2 in the WHERE cannot make use of the whole index.

And if Field1 + Field2 + Field3 is the index, a query with only Field1 & Field3 in WHERE the same would happen.

Option 1

You would have to create a separate index for each search scenario if you want to optimize each search. However, if the tables are large, then the indexes would become extremely large as well. If those search queries are made very often this would be worth it.

Option 2

You can use a nifty trick if your searches have a low selectivity (i.e. Gender) by putting the low selectivity columns to the left most and use Gender IN(M, F) to include it in the WHERE clause along with the other column(s) to make use of the whole index.

For query with criteria

field1 = 10 AND field2 > 0

you need to have composite index field1 + field2 (in this particular order)

It depends on your queries. If you will be doing many queries with field1 and field2 often, then it makes sense to have a composite index. Otherwise you might get by fine on multiple single column indices.

Category:mysql Views:0 Time:2011-07-20
Tags: mysql indexing

Related post

  • Multiple Indexes vs Multi-Column Indexes 2008-10-07

    I've just been adding an Index to a table in SQL Server 2005 and it got me thinking. What is the difference between creating 1 index and defining multiple columns over having 1 index per column you want to index. Are there certain reasons why one sho

  • phpMyAdmin wants to add multiple indices as one multi-column index 2010-10-21

    I'm creating tables using phpMyAdmin and want to define two different columns as indices. I'm not trying to create a multi-column index but phpMyAdmin creates them as such. Are there any possible issues with that? The fields don't relate to each othe

  • Order of columns in a multi-column index in MySQL 2011-01-23

    I'm trying to understand what is better when defining multi-column indexes: Putting the most selective column first (higher cardinality, for speed?); or Putting the less selective column first (Lower cardinality, for index compression?) Or maybe it d

  • Indexes and multi column primary keys 2010-06-15

    Went searching and didn't find the answer to this specific noob question. My apologies if I missed it. In a MySQL database I have a table with the following primary key PRIMARY KEY id (invoice, item) In my application I will also frequently be select

  • SQL Overlapping and Multi-Column Indexes 2010-03-08

    I am attempting to tune some stored procedures and have a question on indexes. I have used the tuning advisor and they recommended two indexes, both for the same table. The issue is one index is for one column and the other is for multiple columns, o

  • How do I specify a multi-column index with Fluent NHibernate 2010-11-06

    The goal here is have my complete database configuration and schema generation handled by fluent nhibernate. Is there a way to to specify a multi-column index with Fluent nhibernate? I understand that you can specify an index on a single property/col

  • How to create a multi column index with descending option 2012-02-10

    create table foo (id, name, col1, col2, col3, ..); create index index_name on foo (col1, col2, col3 desc); How do I know if it has been created in the descending option and how to verify if queries on foo are really using this index? --------------So

  • How to create a Multi-Column Index or Unique Constraint with NHibernate 2009-05-07

    How to create a Multi-Column Index and/or Unique Constraint using NHibernate Mapping or Fluent NHibernate. --------------Solutions------------- assign a index/unique constraint name to more then one property <property name="A" index="AB" />

  • Grails multi column indexes 2009-10-02

    Can someone explain how to define multi column indexes in Grails? The documentation is at best sparse. This for example does not seem to work at all: I've had some luck with this, but the results seems random

  • How to change the column order in a multi-column index using fluent-nhibernate? 2009-12-09

    How to change the column order in a multi-column index? I.e: mapping.References(x => x.SomeReference).SetAttribute("index", "IX_index"); mapping.Map(x => x.SomeField).SetAttribute("index", "IX_index"); Produces the following Schema: create inde

  • How do I create and use (or simulate) multi-column indexes in Erlang Mnesia 2010-12-29

    I have looked through the Mnesia documentation and the 3 popular Erlang books. It seems only single column primary and secondary indexes can be created and used. Or maybe it is just what the examples cover? If I create a separate index on each of the

  • Multi column index implementation 2011-03-23

    Does anyone know how the multi column index is implemented in (B-Tree or whatever)? is the key concatenated or it is something like the kd-tree? --------------Solutions------------- Answer here:

  • Multi-column index performance 2011-11-22

    I have the following query: SELECT * from stop_times WHERE (departure_time BETWEEN '02:41' AND '05:41' OR departure_time BETWEEN '26:41' AND '29:41') AND stop_times.stop_id IN(51511,51509,51508,51510,6,53851,51522,51533) that returns 134 rows in ~800

  • confused terms: cover index, compound index and multi-fields index 2011-08-03

    I am confused with these 3 terms: cover index, compound index and multi-fields index. Are they same things? or each of them has subtle difference? Thanks --------------Solutions------------- Compound index and multi-fields Index are the same. Other t

  • Multi-column index or multiple indexes for timeseries MySQL table? 2012-02-24

    I have a MySQL MyISAM table with about 400 million rows of price data (7GB data + 9GB index) with 3 columns: CREATE TABLE `prices` ( `ts` datetime NOT NULL, `id` int(10) unsigned NOT NULL, `price` double NOT NULL, PRIMARY KEY (`ts`,`instrid`) ) ENGIN

  • Do multi-column indexes affect the size of db-backup or size of mysql Server's RAM or HDD or CPU? 2012-03-06

    I have database1 and database2. database1 does not use any special Index (rather that PRIMARY KEY indexes on id columns, but database2 has been optimized using proper indexes (some indexes on multiple rows in some tables that have a lot of data). Sup

  • How can I make an index that multi column as one index's column? 2009-12-09

    say, I have a table TABLE(col1,col2,col3) and, I want create a index INDEX(col1=table.col1+table.col2) --------------Solutions------------- create table myTable ( pk int(11) not null, col1 varchar(100), col2 varchar(100), col3 varchar(100), key idx_c

  • PostgreSQL Multi-Column index join with comparison ("") operators 2011-02-03

    I'm trying to take advantage of a multi-column btree index in PostgreSQL to perform an annoying join between two tables. Table "revision_main" Column | Type | Modifiers ----------------+------------------------+----------- revision_id | integer | pag

  • Does a multi-column index work for single column selects too? 2009-04-28

    I've got (for example) an index: CREATE INDEX someIndex ON orders (customer, date); Does this index only accelerate queries where customer and date are used or does it accelerate queries for a single-column like this too? SELECT * FROM orders WHERE c

Copyright (C), All Rights Reserved.

processed in 0.091 (s). 11 q(s)