← Back to team overview

maria-discuss team mailing list archive

Re: doubt about index

 

A unique index is just a btree that has a flag that says only one of these
keys in the leaf.

On Mon, Jun 22, 2015 at 7:13 PM, Justin Swanhart <greenlion@xxxxxxxxx>
wrote:

> Well, tokudb is index organized.  spider uses whatever engine is remote.
>  myisam should never be used for anything that you actually care about,
> aria is not stable, there are no other major engines.
>
> On Mon, Jun 22, 2015 at 7:11 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
> wrote:
>
>> that's true if you use innodb, what about tokudb, spider, myisam and
>> others engines? there's specific otimizations to unique keys that could be
>> used with index that always be unique?
>>
>> 2015-06-22 23:08 GMT-03:00 Justin Swanhart <greenlion@xxxxxxxxx>:
>>
>>> Hi, the primary key is carried in secondary indexes, and PK is fastest
>>> method, these indexes make no sense.
>>> That being said, if i is unique, then i,b has to also be unique because
>>> i can never be duplicated in the table.  This means that i,b is
>>> functionally dependent on the primary key, and you should just use the
>>> primary key.
>>>
>>> --Justin
>>>
>>> On Mon, Jun 22, 2015 at 3:53 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
>>> wrote:
>>>
>>>> 2015-06-22 19:47 GMT-03:00 Reindl Harald <h.reindl@xxxxxxxxxxxxx>:
>>>>
>>>>>
>>>>>
>>>>> Am 23.06.2015 um 00:43 schrieb Roberto Spadim:
>>>>>
>>>>>> hi guys, i`m with a doubt...
>>>>>> when i have a primary key i know that's a unique key
>>>>>> when i have a index with primary key + any other column, does mariadb
>>>>>> consider that it's unique too?
>>>>>>
>>>>>
>>>>> if it is defined as unique key yes
>>>>>
>>>>> what is "index with primary key + any other column"?
>>>>>
>>>>
>>>> for example
>>>> create table x(
>>>> i int,
>>>> b int,
>>>> c int, d int , e int, ...
>>>> primary key(i),
>>>> key teste(b,i)
>>>> )
>>>>
>>>> the test index is primary key (i column) + any other column
>>>> (b,c,d,e,...)
>>>>
>>>>
>>>>
>>>>
>>>>> you have two keys in that case and the select uses one of them
>>>>
>>>> yeap but some search algorithms use unique key/non unique key
>>>> information to improve search right?
>>>> does it consider that any index that contains a unique index columns +
>>>> anyother column as "unique"
>>>>
>>>>
>>>>>
>>>>>
>>>>>  i`m thinking more about SELECT optimization
>>>>>>
>>>>>
>>>>> how does it matter if a key is unique or not for select optimization?
>>>>>
>>>>
>>>> select "where i=1" should return 0/1 rows (it's unique),
>>>> "where b=1" should return 0+ rows, but "where b=1 and i=1" should
>>>> return 0/1 rows
>>>>
>>>>
>>>>
>>>> --
>>>> Roberto Spadim
>>>> SPAEmpresarial - Software ERP
>>>> Eng. Automação e Controle
>>>>
>>>> _______________________________________________
>>>> Mailing list: https://launchpad.net/~maria-discuss
>>>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>>> Unsubscribe : https://launchpad.net/~maria-discuss
>>>> More help   : https://help.launchpad.net/ListHelp
>>>>
>>>>
>>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial - Software ERP
>> Eng. Automação e Controle
>>
>
>

Follow ups

References