473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

Mar 1 '06 #1
7 1499
"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
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
>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
>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
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
>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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: teddysnips | last post by:
This from a SQL Server manual: "Complex queries, however, such as those in decision support systems, can reference large numbers of rows in base tables and aggregate large amounts of information...
8
by: **Developer** | last post by:
Seems that Dot.net can not handle files of Indexed Pixel Format as well as other types of formats. Given a file or and Image of type Indexed Pixel Format is there a method to convert it to some...
7
by: leventyilmaz | last post by:
Hi, I have a very simple problem, but do not know an elegant way to accomplish this. ### # I have a list of names: names = # and another set of names that I want to insert into # the...
0
by: xhy_China | last post by:
Hi,I want to ask three questions(in vs.net and C#): 1. how can I know whether a bitmap is an indexed or non-indexed? 2. how can I convert a indexed bitmap to non-indexed bitmap? 3. how can I...
1
by: Mr,Goody | last post by:
hi frends. i have such a great problem.i want to use union in indexed view and i must have to use it. because i cant make a one table because in those two tables there are 2,000,000 records...
8
by: Joergen Bech | last post by:
Suppose I have Dim bm As New Bitmap(16, 16,Imaging.PixelFormat.Format8bppIndexed) I cannot use Dim g As Graphics = Graphics.FromImage(bmdest) Dim hdc As IntPtr = g.GetHdc() as the...
1
by: Jason Wilson | last post by:
I was looking to improve the performance of an ASP.NET application by creating the an indexed view that could be used instead of some of the root tables. What I didn't realize is that it would...
1
by: Nathan Sokalski | last post by:
I have created declared a Bitmap using the following statement: Dim bmp As New Bitmap(100, 100, PixelFormat.Format8bppIndexed) Because the SetPixel() method is disabled and a Graphics object...
17
by: David C. Ullrich | last post by:
Having a hard time phrasing this in the form of a question... The other day I saw a thread where someone asked about overrideable properties and nobody offered the advice that properties are...
25
by: Rick Collard | last post by:
Using DAO 3.6 on an Access 2002 database, I'm getting unexpected results with the FindFirst method. Here's the simple code to test: Public Sub FindIt() Dim db As Database, rs As Recordset...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.