By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,900 Members | 1,078 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,900 IT Pros & Developers. It's quick & easy.

Indexed single-col search capability from multi-col index

P: n/a
Hi All,

I'd like to have indexed search capability on column A, column B, or
columns (A,B) for a given table. According to the MySQL manual, a
multi-column index of (A,B) will provide "leftmost prefix" indexing, so
that I can search on column A uniquely or columns (A,B) together, but
not on column B uniquely. Therefore, I would need a second index on
column B.

My question: would there be any benefit, whatsoever, to having a
separate index just for column A, or is that completely redundant to
the multi-column index (A,B)?

Thanks,
-Dave H.

Mar 1 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"Dave Hammond" <dh****@gmail.com> wrote in message
news:11**********************@t39g2000cwt.googlegr oups.com...
My question: would there be any benefit, whatsoever, to having a
separate index just for column A, or is that completely redundant to
the multi-column index (A,B)?


Indexes take up space on disk and in memory. Theres a "key cache" which
holds index structures in memory. The size of the key cache is finite, and
you can tune the allocation of memory for MySQL's key cache. A
single-column index on A would naturally be smaller than the multi-column
index on (A,B), so more of the index can fit in the same amount of memory.
But if you intend to size the key cache so that the whole (A,B) index fits
in it, then I'd say yes, the additional index is redundant.

Another consideration is whether the (A,B) index is redundant if you also
have indexes on A and B individually. The best reason for keeing the (A,B)
index is if uniqueness is enforced only over both columns instead of either
A or B individually. Another reason to keep the (A,B) index is that MySQL
has a limitation that only one index can be used per table per query. If
you typically have searches that involve both A and B in one query, then an
index over both columns could be beneficial.

Regards,
Bill K.
Mar 1 '06 #2

P: n/a
Dave Hammond wrote:
Hi All,

I'd like to have indexed search capability on column A, column B, or
columns (A,B) for a given table. According to the MySQL manual, a
multi-column index of (A,B) will provide "leftmost prefix" indexing, so
that I can search on column A uniquely or columns (A,B) together, but
not on column B uniquely. Therefore, I would need a second index on
column B.

My question: would there be any benefit, whatsoever, to having a
separate index just for column A, or is that completely redundant to
the multi-column index (A,B)?
Since it does leftmost prefix - what do *you* think? Simple logic
prevails here.

Unless you are talking hundreds of thousands of records, I would
question indexes at all in MySQL. But YMMV.

Do a simple test. insert 100K unique values in a table with and without
indexes and see if you can actually *perceive* any difference in the
select times.

Thanks,
-Dave H.

Mar 2 '06 #3

P: n/a
>The best reason for keeing the (A,B)
index is if uniqueness is enforced only over both columns instead of either
A or B individually. Another reason to keep the (A,B) index is that MySQL
has a limitation that only one index can be used per table per query. If
you typically have searches that involve both A and B in one query, then an
index over both columns could be beneficial.


Thanks, Bill.

The (A,B) index enforces uniqueness, and as such, is required.
Searches of column A only, B only, and A,B combined are equally
probable, so it sounds like two indexes -- multi-column A,B and single
column B -- will be the best way to go.

Mar 2 '06 #4

P: n/a
>Since it does leftmost prefix - what do *you* think? Simple logic
prevails here.
Simple logic clearly says no additional index is necessary, but simple
logic doesn't always account for things like undocumented behaviour.
That's why I solicited the opinions of developers who have more
experience with MySQL than I do.
Unless you are talking hundreds of thousands of records, I would
question indexes at all in MySQL. But YMMV.
In fact, hundreds of thousands of records. A rolling 7 years of data
for per-week sales statistics for over 1000 business units.
Do a simple test. insert 100K unique values in a table with and without
indexes and see if you can actually *perceive* any difference in the
select times.


Agreed... something that I should have done before posting. Mea Culpa.

-Dave H.

Mar 2 '06 #5

P: n/a
Dave Hammond wrote:
Since it does leftmost prefix - what do *you* think? Simple logic
prevails here.

Simple logic clearly says no additional index is necessary, but simple
logic doesn't always account for things like undocumented behaviour.
That's why I solicited the opinions of developers who have more
experience with MySQL than I do.

Unless you are talking hundreds of thousands of records, I would
question indexes at all in MySQL. But YMMV.

In fact, hundreds of thousands of records. A rolling 7 years of data
for per-week sales statistics for over 1000 business units.

Do a simple test. insert 100K unique values in a table with and without
indexes and see if you can actually *perceive* any difference in the
select times.

Agreed... something that I should have done before posting. Mea Culpa.

-Dave H.


Just curious, but does your employer realize that the for about the same
licensing cost of deploying a production MySQL database such as this, he
could purchase Oracle?

A common misconception is that MySQL is FREE - it is as long as you make
your code/application a freeware application under the GPL licensing.
Which means that your competitor can have the same advantage as you
because they now know how you track your sales etc... The reason we
write our own proprietary applications is for the intellectual property
- which you now must give away - for Free... If you sell it - your
customers purchasing the system/application must purchase the MySQL
commercial license.

MySQL is doing a lot of good things, but as a long time DBA (15+ yrs.) -
and not tied to Oracle RDBMS - I do not think it is prime-time ready for
some of the things for which it is being used. - And yes, that is a
professional option not a personal opinion.

For full licensing details seee:
http://www.mysql.com/company/legal/licensing/ or contact their sales
department.
Mar 2 '06 #6

P: n/a
>A common misconception is that MySQL is FREE - it is as long as you make
your code/application a freeware application under the GPL licensing.
Which means that your competitor can have the same advantage as you
because they now know how you track your sales etc... The reason we
write our own proprietary applications is for the intellectual property
- which you now must give away - for Free... If you sell it - your
customers purchasing the system/application must purchase the MySQL
commercial license.


It may well be that, if used in a commercial package, the MySQL license
cost is comparable to Oracle. However, we are not a commercial
software or systems provider. We are a provider of business managament,
payroll funding, and related financial services to specific vertical
markets. MySQL is used in-house in a number of applications which are
accessed exclusively by our corporate employees. Based on previous
discussion with a MySQL sales contact, that usage qualifies for free
licensing.

Mar 2 '06 #7

P: n/a
Dave Hammond wrote:
A common misconception is that MySQL is FREE - it is as long as you make
your code/application a freeware application under the GPL licensing.
Which means that your competitor can have the same advantage as you
because they now know how you track your sales etc... The reason we
write our own proprietary applications is for the intellectual property
- which you now must give away - for Free... If you sell it - your
customers purchasing the system/application must purchase the MySQL
commercial license.

It may well be that, if used in a commercial package, the MySQL license
cost is comparable to Oracle. However, we are not a commercial
software or systems provider. We are a provider of business managament,
payroll funding, and related financial services to specific vertical
markets. MySQL is used in-house in a number of applications which are
accessed exclusively by our corporate employees. Based on previous
discussion with a MySQL sales contact, that usage qualifies for free
licensing.


If they are truly following the wording of their license agreement, I
would disagree with him/her - and you better get it in writing from the
sales person -- unless you are making your application freely available
- OUTSIDE of your company via a GPL license - you must purchase a
Commercial license.

From the "MySQL License Policy" page:

"For OEM's, ISVs, corporate, and government users, a commercial license
is the proper solution because it provides you with assurance from the
vendor and releases you from the strict requirements of the GPL license.

Nevertheless, you can test MySQL under the GPL license and inspect the
source code before you purchase a commercial non-GPL license."

Note: There is a seperate category for CORPORATE USERS (that is your
company), so, according to this you must purchase the license.

Again, I would get it in writing (NOT AN EMAIL) - so that in the event
you are ever audited by MySQL - you have some leg to stand on.
Mar 3 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.