← Back to team overview

maria-discuss team mailing list archive

Re: doubt about index

 

Yes sorry, I just realized I wrote something stupid: the index is not unique.
Roberto, maybe you meant that teste is unique because you know it is unique? But then, declare it as unique. But don't expect the query plans to change.

Federico



--------------------------------------------
Mar 23/6/15, Reindl Harald <h.reindl@xxxxxxxxxxxxx> ha scritto:

 Oggetto: Re: [Maria-discuss] doubt about index
 A: maria-discuss@xxxxxxxxxxxxxxxxxxx
 Data: Martedì 23 giugno 2015, 01:31
 
 
 
 Am 23.06.2015 um 01:20 schrieb Federico
 Razzoli:
 > I still don't understand
 the matter.
 >
 >
 "teste" is a unique index, because part of it is
 unique
 
 no it is not
 *because* only a part of it is unique
 
 > But it is not declared as unique, and
 MariaDB is not suppose to read our minds.
 
 hence it is not
 
 > The query written by
 Reindl looks realistic, but it would use the primary key,
 and I would be disappointed if it used "teste".
 
 i won't and that is
 *clearly* statet in the docs
 
 > Are we talking about real-world needs, or
 just speculation?
 
 likely
 speculation
 
 >
 --------------------------------------------
 > Mar 23/6/15, Reindl Harald <h.reindl@xxxxxxxxxxxxx>
 ha scritto:
 >
 >   Oggetto: Re: [Maria-discuss]
 doubt about index
 >   A:
 "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
 >   Data: Martedì 23 giugno
 2015, 01:13
 >
 >
 >
 >   Am
 23.06.2015 um 00:53 schrieb Roberto
 >   Spadim:
 >   > 2015-06-22 19:47
 GMT-03:00
 >   Reindl Harald
 <h.reindl@xxxxxxxxxxxxx
 >   > <mailto: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,...)
 >
 >   these are two indexes
 >
 >   the
 second one is completly nosense as long
 >   your qquery is not in the
 >   form of
 >   "where b=x and i=y"
 and even "where i=y and
 >   b=x" won't be
 able
 >   to use it
 >
 >   >>  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?
 >
 >   which part of the
 >   documentation says that?
 >
 >   >
 does it consider that any index that
 >   contains a unique index
 columns +
 >   >
 >   anyother column as
 "unique"
 >
 >   why should it?
 >   it must not
 >   just because they are not
 >
 >   > 
        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
 >
 >   it don't
 >   matter at all, these are two
 seperate indexes
 
 -----Segue allegato-----
 
 _______________________________________________
 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



References