Indexed single-col search capability from multi-col index

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.

Re: Indexed single-col search capability from multi-col index

"Dave Hammond" wrote in message
news:1141220445.962218.285800@t39g2000cwt.googlegroups.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.

Re: Indexed single-col search capability from multi-col index

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

Re: Indexed single-col search capability from multi-col index

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

Re: Indexed single-col search capability from multi-col index

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

Re: Indexed single-col search capability from multi-col index

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.

Re: Indexed single-col search capability from multi-col index

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

Re: Indexed single-col search capability from multi-col index

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.