[Maria-discuss] doubt about index

newer
[Maria-discuss] New Question: ipv6

older
[Maria-discuss] FedereratedX ODBC...

Roberto Spadim

23 Jun 2015 23 Jun '15

12:43 a.m.

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? i`m thinking more about SELECT optimization

Attachments:

attachment.html (text/html — 289 bytes)

Show replies by date

Reindl Harald

23 Jun 23 Jun

12:47 a.m.

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"? you have two keys in that case and the select uses one of them

...

i`m thinking more about SELECT optimization

how does it matter if a key is unique or not for select optimization?

Roberto Spadim

12:53 a.m.

2015-06-22 19:47 GMT-03:00 Reindl Harald :

...

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

Federico Razzoli

1:07 a.m.

That's a strange index. In InnoDB, tables are organized by primary key, so ALL indexes contain primary keys values. In other storage engines... well, I can't think a use case when that index would make sense. Maybe a more realistic example would help :) Federico -------------------------------------------- Mar 23/6/15, Roberto Spadim ha scritto: Oggetto: Re: [Maria-discuss] doubt about index A: "Reindl Harald" Cc: "Maria Discuss" Data: Martedì 23 giugno 2015, 00:53 2015-06-22 19:47 GMT-03:00 Reindl Harald : 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 examplecreate 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 themyeap 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 ERPEng. Automação e Controle -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Reindl Harald

1:13 a.m.

Am 23.06.2015 um 00:53 schrieb Roberto Spadim:

...

2015-06-22 19:47 GMT-03:00 Reindl Harald mailto:h.reindl@thelounge.net>:

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

Federico Razzoli

1:20 a.m.

I still don't understand the matter. "teste" is a unique index, because part of it is unique. But it is not declared as unique, and MariaDB is not suppose to read our minds. The query written by Reindl looks realistic, but it would use the primary key, and I would be disappointed if it used "teste". Are we talking about real-world needs, or just speculation? Federico -------------------------------------------- Mar 23/6/15, Reindl Harald ha scritto: Oggetto: Re: [Maria-discuss] doubt about index A: "Maria Discuss" 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 mailto:h.reindl@thelounge.net>:

Am 23.06.2015 um 00:43 schrieb Roberto Spadim:

...

when i have a

...

   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

hi guys, i`m with a doubt... primary key i know that's a unique key 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Reindl Harald

1:31 a.m.

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 ha scritto:

Oggetto: Re: [Maria-discuss] doubt about index A: "Maria Discuss" 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 mailto:h.reindl@thelounge.net>:

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

Federico Razzoli

1:44 a.m.

...

I still don't understand

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 ha scritto: Oggetto: Re: [Maria-discuss] doubt about index A: maria-discuss@lists.launchpad.net Data: Martedì 23 giugno 2015, 01:31 Am 23.06.2015 um 01:20 schrieb Federico Razzoli: 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 ha scritto:

Oggetto: Re: [Maria-discuss] doubt about index A: "Maria Discuss" 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 mailto:h.reindl@thelounge.net>: > > 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Justin Swanhart

4:08 a.m.

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 wrote:

...

2015-06-22 19:47 GMT-03:00 Reindl Harald :

...
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Roberto Spadim

4:11 a.m.

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 :

...

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 wrote:

...
2015-06-22 19:47 GMT-03:00 Reindl Harald :

...
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Justin Swanhart

4:13 a.m.

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

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

...
2015-06-22 19:47 GMT-03:00 Reindl Harald :

...
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Justin Swanhart

4:14 a.m.

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

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

...
2015-06-22 19:47 GMT-03:00 Reindl Harald :

...
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Roberto Spadim

4:20 a.m.

and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong?

Justin Swanhart

4:39 a.m.

Hi, If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that. --Justin On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim wrote:

...

and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select

in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong?

Justin Swanhart

4:42 a.m.

Hi, b+tree search is binary search, which is divide and conquer. Saving scanning a key is like sneezing in a hurricane, you still are o(log(n)) in. On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart wrote:

...

Hi,

If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that.

--Justin

On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim wrote:

...
and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select

in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong?

Roberto Spadim

4:43 a.m.

yeap, i just want know if the unique allow stop the conquer at first result (unique key) or if it continue (non unique key) 2015-06-22 23:42 GMT-03:00 Justin Swanhart :

...

Hi,

b+tree search is binary search, which is divide and conquer. Saving scanning a key is like sneezing in a hurricane, you still are o(log(n)) in.

On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart wrote:

...
Hi,

If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that.

--Justin

On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim wrote:

...
and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select

in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong?

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Justin Swanhart

4:44 a.m.

No, you have to look in the leaf! On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim wrote:

...

yeap, i just want know if the unique allow stop the conquer at first result (unique key) or if it continue (non unique key)

2015-06-22 23:42 GMT-03:00 Justin Swanhart :

...
Hi,

b+tree search is binary search, which is divide and conquer. Saving scanning a key is like sneezing in a hurricane, you still are o(log(n)) in.

On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart wrote:

...
Hi,

If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that.

--Justin

On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim wrote:

...
and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select

in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong?

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Justin Swanhart

4:45 a.m.

you are probably thinking of b-trees which are unbalanced. b+tree is o(log(n)) based on depth of tree and you always have to go to bottom to get leaf. On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart wrote:

...

No, you have to look in the leaf!

On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim wrote:

...
yeap, i just want know if the unique allow stop the conquer at first result (unique key) or if it continue (non unique key)

2015-06-22 23:42 GMT-03:00 Justin Swanhart :

...
Hi,

b+tree search is binary search, which is divide and conquer. Saving scanning a key is like sneezing in a hurricane, you still are o(log(n)) in.

On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart wrote:

...
Hi,

If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that.

--Justin

On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim wrote:

...
and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select

in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong?

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Roberto Spadim

4:47 a.m.

yeap at optimizer part of mariadb, is there some check about clustered/nonclustered/rtree/hash index to select best index or not? 2015-06-22 23:45 GMT-03:00 Justin Swanhart :

...

you are probably thinking of b-trees which are unbalanced. b+tree is o(log(n)) based on depth of tree and you always have to go to bottom to get leaf.

On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart wrote:

...
No, you have to look in the leaf!

On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim wrote:

...
yeap, i just want know if the unique allow stop the conquer at first result (unique key) or if it continue (non unique key)

2015-06-22 23:42 GMT-03:00 Justin Swanhart :

...
Hi,

b+tree search is binary search, which is divide and conquer. Saving scanning a key is like sneezing in a hurricane, you still are o(log(n)) in.

On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart wrote:

...
Hi,

If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that.

--Justin

On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim
...
wrote:

...
and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select

in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong?

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Justin Swanhart

4:50 a.m.

If you have the PK in your query (unless the db does something perverse with order by (which is common)), then the PK will be used. Sometimes the order by thing bites in other direction, and PK is used for desc or asc access to table when another index could be used. Hash indexes are only on memory, and are preferred over b-tree unless ranges are performed because hash is O(1) and b-tree is O(log(n)). rtree and fulltext indexes can only be used with particular functions. non-clustered indexes are used in all other cases, sometimes with ICP. When you have more than one index to choose that could be effective, the histograms (statistics) for the tables are compared and the database tries to decide which index will perform best (cost based optimization). --Justin On Mon, Jun 22, 2015 at 7:47 PM, Roberto Spadim wrote:

...

yeap

at optimizer part of mariadb, is there some check about clustered/nonclustered/rtree/hash index to select best index or not?

2015-06-22 23:45 GMT-03:00 Justin Swanhart :

...
you are probably thinking of b-trees which are unbalanced. b+tree is o(log(n)) based on depth of tree and you always have to go to bottom to get leaf.

On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart wrote:

...
No, you have to look in the leaf!

On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim wrote:

...
yeap, i just want know if the unique allow stop the conquer at first result (unique key) or if it continue (non unique key)

2015-06-22 23:42 GMT-03:00 Justin Swanhart :

...
Hi,

b+tree search is binary search, which is divide and conquer. Saving scanning a key is like sneezing in a hurricane, you still are o(log(n)) in.

On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart wrote:

...
Hi,

If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that.

--Justin

On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim < roberto@spadim.com.br> wrote:

> and there's optimization with this flag? i see that equal could be > optimized cause if we found 1 row we can return to user that row and stop > select > > in other words, if i see a index with primary key or unique columns, > could i change the index to unique and get some kind of performace? today i > think not ,cause unique key check if it's really unique (for example at > insert/update/replace), i'm wrong? >

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Roberto Spadim

5:01 a.m.

ehhe that's the point, how to remove perverse order by, today i'm using temporary table and after ordering, but i don't like this "solution" 2015-06-22 23:50 GMT-03:00 Justin Swanhart :

...

If you have the PK in your query (unless the db does something perverse with order by (which is common)), then the PK will be used. Sometimes the order by thing bites in other direction, and PK is used for desc or asc access to table when another index could be used. Hash indexes are only on memory, and are preferred over b-tree unless ranges are performed because hash is O(1) and b-tree is O(log(n)). rtree and fulltext indexes can only be used with particular functions. non-clustered indexes are used in all other cases, sometimes with ICP.

When you have more than one index to choose that could be effective, the histograms (statistics) for the tables are compared and the database tries to decide which index will perform best (cost based optimization).

--Justin

On Mon, Jun 22, 2015 at 7:47 PM, Roberto Spadim wrote:

...
yeap

at optimizer part of mariadb, is there some check about clustered/nonclustered/rtree/hash index to select best index or not?

2015-06-22 23:45 GMT-03:00 Justin Swanhart :

...
you are probably thinking of b-trees which are unbalanced. b+tree is o(log(n)) based on depth of tree and you always have to go to bottom to get leaf.

On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart wrote:

...
No, you have to look in the leaf!

On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim wrote:

...
yeap, i just want know if the unique allow stop the conquer at first result (unique key) or if it continue (non unique key)

2015-06-22 23:42 GMT-03:00 Justin Swanhart :

...
Hi,

b+tree search is binary search, which is divide and conquer. Saving scanning a key is like sneezing in a hurricane, you still are o(log(n)) in.

On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart wrote:

> Hi, > > If you do a code inspection you will find that uniqueness is checked > on insertion, and the database cares not about it after that. > > --Justin > > On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim < > roberto@spadim.com.br> wrote: > >> and there's optimization with this flag? i see that equal could be >> optimized cause if we found 1 row we can return to user that row and stop >> select >> >> in other words, if i see a index with primary key or unique >> columns, could i change the index to unique and get some kind of >> performace? today i think not ,cause unique key check if it's really unique >> (for example at insert/update/replace), i'm wrong? >> > >

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Roberto Spadim

5:03 a.m.

the other solution is "force index()" but i'm trying to understand why it choose a 'bad' index 2015-06-23 0:01 GMT-03:00 Roberto Spadim :

...

ehhe that's the point, how to remove perverse order by, today i'm using temporary table and after ordering, but i don't like this "solution"

2015-06-22 23:50 GMT-03:00 Justin Swanhart :

...
If you have the PK in your query (unless the db does something perverse with order by (which is common)), then the PK will be used. Sometimes the order by thing bites in other direction, and PK is used for desc or asc access to table when another index could be used. Hash indexes are only on memory, and are preferred over b-tree unless ranges are performed because hash is O(1) and b-tree is O(log(n)). rtree and fulltext indexes can only be used with particular functions. non-clustered indexes are used in all other cases, sometimes with ICP.

When you have more than one index to choose that could be effective, the histograms (statistics) for the tables are compared and the database tries to decide which index will perform best (cost based optimization).

--Justin

On Mon, Jun 22, 2015 at 7:47 PM, Roberto Spadim wrote:

...
yeap

at optimizer part of mariadb, is there some check about clustered/nonclustered/rtree/hash index to select best index or not?

2015-06-22 23:45 GMT-03:00 Justin Swanhart :

...
you are probably thinking of b-trees which are unbalanced. b+tree is o(log(n)) based on depth of tree and you always have to go to bottom to get leaf.

On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart wrote:

...
No, you have to look in the leaf!

On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim
...
wrote:

...
yeap, i just want know if the unique allow stop the conquer at first result (unique key) or if it continue (non unique key)

2015-06-22 23:42 GMT-03:00 Justin Swanhart :

> Hi, > > b+tree search is binary search, which is divide and conquer. Saving > scanning a key is like sneezing in a hurricane, you still are o(log(n)) in. > > On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart < > greenlion@gmail.com> wrote: > >> Hi, >> >> If you do a code inspection you will find that uniqueness is >> checked on insertion, and the database cares not about it after that. >> >> --Justin >> >> On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim < >> roberto@spadim.com.br> wrote: >> >>> and there's optimization with this flag? i see that equal could be >>> optimized cause if we found 1 row we can return to user that row and stop >>> select >>> >>> in other words, if i see a index with primary key or unique >>> columns, could i change the index to unique and get some kind of >>> performace? today i think not ,cause unique key check if it's really unique >>> (for example at insert/update/replace), i'm wrong? >>> >> >> >

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Justin Swanhart

3:18 p.m.

Hi, You can't fix that with a flag that says an index is unique. It is a big problem. Domas opened a bug on a variety of cases of it recently. On Mon, Jun 22, 2015 at 8:03 PM, Roberto Spadim wrote:

...

the other solution is "force index()" but i'm trying to understand why it choose a 'bad' index

2015-06-23 0:01 GMT-03:00 Roberto Spadim :

...
ehhe that's the point, how to remove perverse order by, today i'm using temporary table and after ordering, but i don't like this "solution"

2015-06-22 23:50 GMT-03:00 Justin Swanhart :

...
If you have the PK in your query (unless the db does something perverse with order by (which is common)), then the PK will be used. Sometimes the order by thing bites in other direction, and PK is used for desc or asc access to table when another index could be used. Hash indexes are only on memory, and are preferred over b-tree unless ranges are performed because hash is O(1) and b-tree is O(log(n)). rtree and fulltext indexes can only be used with particular functions. non-clustered indexes are used in all other cases, sometimes with ICP.

When you have more than one index to choose that could be effective, the histograms (statistics) for the tables are compared and the database tries to decide which index will perform best (cost based optimization).

--Justin

On Mon, Jun 22, 2015 at 7:47 PM, Roberto Spadim wrote:

...
yeap

at optimizer part of mariadb, is there some check about clustered/nonclustered/rtree/hash index to select best index or not?

2015-06-22 23:45 GMT-03:00 Justin Swanhart :

...
you are probably thinking of b-trees which are unbalanced. b+tree is o(log(n)) based on depth of tree and you always have to go to bottom to get leaf.

On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart wrote:

...
No, you have to look in the leaf!

On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim < roberto@spadim.com.br> wrote:

> yeap, i just want know if the unique allow stop the conquer at first > result (unique key) or if it continue (non unique key) > > 2015-06-22 23:42 GMT-03:00 Justin Swanhart : > >> Hi, >> >> b+tree search is binary search, which is divide and conquer. >> Saving scanning a key is like sneezing in a hurricane, you still are >> o(log(n)) in. >> >> On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart < >> greenlion@gmail.com> wrote: >> >>> Hi, >>> >>> If you do a code inspection you will find that uniqueness is >>> checked on insertion, and the database cares not about it after that. >>> >>> --Justin >>> >>> On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim < >>> roberto@spadim.com.br> wrote: >>> >>>> and there's optimization with this flag? i see that equal could >>>> be optimized cause if we found 1 row we can return to user that row and >>>> stop select >>>> >>>> in other words, if i see a index with primary key or unique >>>> columns, could i change the index to unique and get some kind of >>>> performace? today i think not ,cause unique key check if it's really unique >>>> (for example at insert/update/replace), i'm wrong? >>>> >>> >>> >> > > > -- > Roberto Spadim > SPAEmpresarial - Software ERP > Eng. Automação e Controle >

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Roberto Spadim

26 Jun 26 Jun

4:44 a.m.

could this be helped by a optimizer_switch to don't optimize using order by? 2015-06-23 10:18 GMT-03:00 Justin Swanhart :

...

Hi,

You can't fix that with a flag that says an index is unique. It is a big problem. Domas opened a bug on a variety of cases of it recently.

On Mon, Jun 22, 2015 at 8:03 PM, Roberto Spadim wrote:

...
the other solution is "force index()" but i'm trying to understand why it choose a 'bad' index

2015-06-23 0:01 GMT-03:00 Roberto Spadim :

...
ehhe that's the point, how to remove perverse order by, today i'm using temporary table and after ordering, but i don't like this "solution"

2015-06-22 23:50 GMT-03:00 Justin Swanhart :

...
If you have the PK in your query (unless the db does something perverse with order by (which is common)), then the PK will be used. Sometimes the order by thing bites in other direction, and PK is used for desc or asc access to table when another index could be used. Hash indexes are only on memory, and are preferred over b-tree unless ranges are performed because hash is O(1) and b-tree is O(log(n)). rtree and fulltext indexes can only be used with particular functions. non-clustered indexes are used in all other cases, sometimes with ICP.

When you have more than one index to choose that could be effective, the histograms (statistics) for the tables are compared and the database tries to decide which index will perform best (cost based optimization).

--Justin

On Mon, Jun 22, 2015 at 7:47 PM, Roberto Spadim wrote:

...
yeap

at optimizer part of mariadb, is there some check about clustered/nonclustered/rtree/hash index to select best index or not?

2015-06-22 23:45 GMT-03:00 Justin Swanhart :

...
you are probably thinking of b-trees which are unbalanced. b+tree is o(log(n)) based on depth of tree and you always have to go to bottom to get leaf.

On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart wrote:

> No, you have to look in the leaf! > > On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim < > roberto@spadim.com.br> wrote: > >> yeap, i just want know if the unique allow stop the conquer at >> first result (unique key) or if it continue (non unique key) >> >> 2015-06-22 23:42 GMT-03:00 Justin Swanhart : >> >>> Hi, >>> >>> b+tree search is binary search, which is divide and conquer. >>> Saving scanning a key is like sneezing in a hurricane, you still are >>> o(log(n)) in. >>> >>> On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart < >>> greenlion@gmail.com> wrote: >>> >>>> Hi, >>>> >>>> If you do a code inspection you will find that uniqueness is >>>> checked on insertion, and the database cares not about it after that. >>>> >>>> --Justin >>>> >>>> On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim < >>>> roberto@spadim.com.br> wrote: >>>> >>>>> and there's optimization with this flag? i see that equal could >>>>> be optimized cause if we found 1 row we can return to user that row and >>>>> stop select >>>>> >>>>> in other words, if i see a index with primary key or unique >>>>> columns, could i change the index to unique and get some kind of >>>>> performace? today i think not ,cause unique key check if it's really unique >>>>> (for example at insert/update/replace), i'm wrong? >>>>> >>>> >>>> >>> >> >> >> -- >> Roberto Spadim >> SPAEmpresarial - Software ERP >> Eng. Automação e Controle >> > >

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Justin Swanhart

6:38 a.m.

Ignore index Sent from my iPhone

...

On Jun 25, 2015, at 7:44 PM, Roberto Spadim wrote:

could this be helped by a optimizer_switch to don't optimize using order by?

2015-06-23 10:18 GMT-03:00 Justin Swanhart :

...
Hi,

You can't fix that with a flag that says an index is unique. It is a big problem. Domas opened a bug on a variety of cases of it recently.

...
On Mon, Jun 22, 2015 at 8:03 PM, Roberto Spadim wrote: the other solution is "force index()" but i'm trying to understand why it choose a 'bad' index

2015-06-23 0:01 GMT-03:00 Roberto Spadim :

...
ehhe that's the point, how to remove perverse order by, today i'm using temporary table and after ordering, but i don't like this "solution"

2015-06-22 23:50 GMT-03:00 Justin Swanhart :

...
If you have the PK in your query (unless the db does something perverse with order by (which is common)), then the PK will be used. Sometimes the order by thing bites in other direction, and PK is used for desc or asc access to table when another index could be used. Hash indexes are only on memory, and are preferred over b-tree unless ranges are performed because hash is O(1) and b-tree is O(log(n)). rtree and fulltext indexes can only be used with particular functions. non-clustered indexes are used in all other cases, sometimes with ICP.

When you have more than one index to choose that could be effective, the histograms (statistics) for the tables are compared and the database tries to decide which index will perform best (cost based optimization).

--Justin

...
On Mon, Jun 22, 2015 at 7:47 PM, Roberto Spadim wrote: yeap

at optimizer part of mariadb, is there some check about clustered/nonclustered/rtree/hash index to select best index or not?

2015-06-22 23:45 GMT-03:00 Justin Swanhart : > you are probably thinking of b-trees which are unbalanced. b+tree is o(log(n)) based on depth of tree and you always have to go to bottom to get leaf. > >> On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart wrote: >> No, you have to look in the leaf! >> >>> On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim wrote: >>> yeap, i just want know if the unique allow stop the conquer at first result (unique key) or if it continue (non unique key) >>> >>> 2015-06-22 23:42 GMT-03:00 Justin Swanhart : >>>> Hi, >>>> >>>> b+tree search is binary search, which is divide and conquer. Saving scanning a key is like sneezing in a hurricane, you still are o(log(n)) in. >>>> >>>>> On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart wrote: >>>>> Hi, >>>>> >>>>> If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that. >>>>> >>>>> --Justin >>>>> >>>>>> On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim wrote: >>>>>> and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select >>>>>> >>>>>> in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong? >>> >>> >>> >>> -- >>> Roberto Spadim >>> SPAEmpresarial - Software ERP >>> Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Roberto Spadim

7:46 a.m.

but check my idea, for example... select some_fields... from table where (where 2,3,4,5,6+ index could be used) order by (where 1 index could be used) optimizer prefer the order by index, i want that optimizer ignore the order by index option (it doens't exists today) does it make sense? 2015-06-26 1:38 GMT-03:00 Justin Swanhart :

...

Ignore index

Sent from my iPhone

On Jun 25, 2015, at 7:44 PM, Roberto Spadim wrote:

could this be helped by a optimizer_switch to don't optimize using order by?

2015-06-23 10:18 GMT-03:00 Justin Swanhart :

...
Hi,

You can't fix that with a flag that says an index is unique. It is a big problem. Domas opened a bug on a variety of cases of it recently.

On Mon, Jun 22, 2015 at 8:03 PM, Roberto Spadim wrote:

...
the other solution is "force index()" but i'm trying to understand why it choose a 'bad' index

2015-06-23 0:01 GMT-03:00 Roberto Spadim :

...
ehhe that's the point, how to remove perverse order by, today i'm using temporary table and after ordering, but i don't like this "solution"

2015-06-22 23:50 GMT-03:00 Justin Swanhart :

...
If you have the PK in your query (unless the db does something perverse with order by (which is common)), then the PK will be used. Sometimes the order by thing bites in other direction, and PK is used for desc or asc access to table when another index could be used. Hash indexes are only on memory, and are preferred over b-tree unless ranges are performed because hash is O(1) and b-tree is O(log(n)). rtree and fulltext indexes can only be used with particular functions. non-clustered indexes are used in all other cases, sometimes with ICP.

When you have more than one index to choose that could be effective, the histograms (statistics) for the tables are compared and the database tries to decide which index will perform best (cost based optimization).

--Justin

On Mon, Jun 22, 2015 at 7:47 PM, Roberto Spadim
...
wrote:

...
yeap

at optimizer part of mariadb, is there some check about clustered/nonclustered/rtree/hash index to select best index or not?

2015-06-22 23:45 GMT-03:00 Justin Swanhart :

> you are probably thinking of b-trees which are unbalanced. b+tree > is o(log(n)) based on depth of tree and you always have to go to bottom to > get leaf. > > On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart < > greenlion@gmail.com> wrote: > >> No, you have to look in the leaf! >> >> On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim < >> roberto@spadim.com.br> wrote: >> >>> yeap, i just want know if the unique allow stop the conquer at >>> first result (unique key) or if it continue (non unique key) >>> >>> 2015-06-22 23:42 GMT-03:00 Justin Swanhart : >>> >>>> Hi, >>>> >>>> b+tree search is binary search, which is divide and conquer. >>>> Saving scanning a key is like sneezing in a hurricane, you still are >>>> o(log(n)) in. >>>> >>>> On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart < >>>> greenlion@gmail.com> wrote: >>>> >>>>> Hi, >>>>> >>>>> If you do a code inspection you will find that uniqueness is >>>>> checked on insertion, and the database cares not about it after that. >>>>> >>>>> --Justin >>>>> >>>>> On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim < >>>>> roberto@spadim.com.br> wrote: >>>>> >>>>>> and there's optimization with this flag? i see that equal could >>>>>> be optimized cause if we found 1 row we can return to user that row and >>>>>> stop select >>>>>> >>>>>> in other words, if i see a index with primary key or unique >>>>>> columns, could i change the index to unique and get some kind of >>>>>> performace? today i think not ,cause unique key check if it's really unique >>>>>> (for example at insert/update/replace), i'm wrong? >>>>>> >>>>> >>>>> >>>> >>> >>> >>> -- >>> Roberto Spadim >>> SPAEmpresarial - Software ERP >>> Eng. Automação e Controle >>> >> >> >

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Justin Swanhart

9:42 a.m.

The ignore index hint. That will ignore the index. Sent from my iPhone

...

On Jun 25, 2015, at 10:46 PM, Roberto Spadim wrote:

but check my idea, for example...

select some_fields... from table where (where 2,3,4,5,6+ index could be used) order by (where 1 index could be used)

optimizer prefer the order by index, i want that optimizer ignore the order by index option (it doens't exists today) does it make sense?

2015-06-26 1:38 GMT-03:00 Justin Swanhart :

...
Ignore index

Sent from my iPhone

...
On Jun 25, 2015, at 7:44 PM, Roberto Spadim wrote:

could this be helped by a optimizer_switch to don't optimize using order by?

2015-06-23 10:18 GMT-03:00 Justin Swanhart :

...
Hi,

You can't fix that with a flag that says an index is unique. It is a big problem. Domas opened a bug on a variety of cases of it recently.

...
On Mon, Jun 22, 2015 at 8:03 PM, Roberto Spadim wrote: the other solution is "force index()" but i'm trying to understand why it choose a 'bad' index

2015-06-23 0:01 GMT-03:00 Roberto Spadim :

...
ehhe that's the point, how to remove perverse order by, today i'm using temporary table and after ordering, but i don't like this "solution"

2015-06-22 23:50 GMT-03:00 Justin Swanhart : > If you have the PK in your query (unless the db does something perverse with order by (which is common)), then the PK will be used. Sometimes the order by thing bites in other direction, and PK is used for desc or asc access to table when another index could be used. Hash indexes are only on memory, and are preferred over b-tree unless ranges are performed because hash is O(1) and b-tree is O(log(n)). rtree and fulltext indexes can only be used with particular functions. non-clustered indexes are used in all other cases, sometimes with ICP. > > When you have more than one index to choose that could be effective, the histograms (statistics) for the tables are compared and the database tries to decide which index will perform best (cost based optimization). > > --Justin > >> On Mon, Jun 22, 2015 at 7:47 PM, Roberto Spadim wrote: >> yeap >> >> at optimizer part of mariadb, is there some check about clustered/nonclustered/rtree/hash index to select best index or not? >> >> 2015-06-22 23:45 GMT-03:00 Justin Swanhart : >>> you are probably thinking of b-trees which are unbalanced. b+tree is o(log(n)) based on depth of tree and you always have to go to bottom to get leaf. >>> >>>> On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart wrote: >>>> No, you have to look in the leaf! >>>> >>>>> On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim wrote: >>>>> yeap, i just want know if the unique allow stop the conquer at first result (unique key) or if it continue (non unique key) >>>>> >>>>> 2015-06-22 23:42 GMT-03:00 Justin Swanhart : >>>>>> Hi, >>>>>> >>>>>> b+tree search is binary search, which is divide and conquer. Saving scanning a key is like sneezing in a hurricane, you still are o(log(n)) in. >>>>>> >>>>>>> On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart wrote: >>>>>>> Hi, >>>>>>> >>>>>>> If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that. >>>>>>> >>>>>>> --Justin >>>>>>> >>>>>>>> On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim wrote: >>>>>>>> and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select >>>>>>>> >>>>>>>> in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong? >>>>> >>>>> >>>>> >>>>> -- >>>>> Roberto Spadim >>>>> SPAEmpresarial - Software ERP >>>>> Eng. Automação e Controle >> >> >> >> -- >> Roberto Spadim >> SPAEmpresarial - Software ERP >> Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Roberto Spadim

10:09 a.m.

the problem is, i don't know whats the index of order by without knowing the sql, the 'create' table and the optimizer choise/options but i want that optimizer avoid the order by index with a optimizer_switch configuration or a sql hint i don't know if it's possible, i don't want to add a FROM table IGNORE INDEX (some index), i want something like ORDER BY IGNORE INDEXES some_columns 2015-06-26 4:42 GMT-03:00 Justin Swanhart :

...

The ignore index hint. That will ignore the index.

Sent from my iPhone

On Jun 25, 2015, at 10:46 PM, Roberto Spadim wrote:

but check my idea, for example...

select some_fields... from table where (where 2,3,4,5,6+ index could be used) order by (where 1 index could be used)

optimizer prefer the order by index, i want that optimizer ignore the order by index option (it doens't exists today) does it make sense?

2015-06-26 1:38 GMT-03:00 Justin Swanhart :

...
Ignore index

Sent from my iPhone

On Jun 25, 2015, at 7:44 PM, Roberto Spadim wrote:

could this be helped by a optimizer_switch to don't optimize using order by?

2015-06-23 10:18 GMT-03:00 Justin Swanhart :

...
Hi,

You can't fix that with a flag that says an index is unique. It is a big problem. Domas opened a bug on a variety of cases of it recently.

On Mon, Jun 22, 2015 at 8:03 PM, Roberto Spadim wrote:

...
the other solution is "force index()" but i'm trying to understand why it choose a 'bad' index

2015-06-23 0:01 GMT-03:00 Roberto Spadim :

...
ehhe that's the point, how to remove perverse order by, today i'm using temporary table and after ordering, but i don't like this "solution"

2015-06-22 23:50 GMT-03:00 Justin Swanhart :

...
If you have the PK in your query (unless the db does something perverse with order by (which is common)), then the PK will be used. Sometimes the order by thing bites in other direction, and PK is used for desc or asc access to table when another index could be used. Hash indexes are only on memory, and are preferred over b-tree unless ranges are performed because hash is O(1) and b-tree is O(log(n)). rtree and fulltext indexes can only be used with particular functions. non-clustered indexes are used in all other cases, sometimes with ICP.

When you have more than one index to choose that could be effective, the histograms (statistics) for the tables are compared and the database tries to decide which index will perform best (cost based optimization).

--Justin

On Mon, Jun 22, 2015 at 7:47 PM, Roberto Spadim < roberto@spadim.com.br> wrote:

> yeap > > at optimizer part of mariadb, is there some check about > clustered/nonclustered/rtree/hash index to select best index or not? > > 2015-06-22 23:45 GMT-03:00 Justin Swanhart : > >> you are probably thinking of b-trees which are unbalanced. b+tree >> is o(log(n)) based on depth of tree and you always have to go to bottom to >> get leaf. >> >> On Mon, Jun 22, 2015 at 7:44 PM, Justin Swanhart < >> greenlion@gmail.com> wrote: >> >>> No, you have to look in the leaf! >>> >>> On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim < >>> roberto@spadim.com.br> wrote: >>> >>>> yeap, i just want know if the unique allow stop the conquer at >>>> first result (unique key) or if it continue (non unique key) >>>> >>>> 2015-06-22 23:42 GMT-03:00 Justin Swanhart : >>>> >>>>> Hi, >>>>> >>>>> b+tree search is binary search, which is divide and conquer. >>>>> Saving scanning a key is like sneezing in a hurricane, you still are >>>>> o(log(n)) in. >>>>> >>>>> On Mon, Jun 22, 2015 at 7:39 PM, Justin Swanhart < >>>>> greenlion@gmail.com> wrote: >>>>> >>>>>> Hi, >>>>>> >>>>>> If you do a code inspection you will find that uniqueness is >>>>>> checked on insertion, and the database cares not about it after that. >>>>>> >>>>>> --Justin >>>>>> >>>>>> On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim < >>>>>> roberto@spadim.com.br> wrote: >>>>>> >>>>>>> and there's optimization with this flag? i see that equal >>>>>>> could be optimized cause if we found 1 row we can return to user that row >>>>>>> and stop select >>>>>>> >>>>>>> in other words, if i see a index with primary key or unique >>>>>>> columns, could i change the index to unique and get some kind of >>>>>>> performace? today i think not ,cause unique key check if it's really unique >>>>>>> (for example at insert/update/replace), i'm wrong? >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>>> -- >>>> Roberto Spadim >>>> SPAEmpresarial - Software ERP >>>> Eng. Automação e Controle >>>> >>> >>> >> > > > -- > Roberto Spadim > SPAEmpresarial - Software ERP > Eng. Automação e Controle >

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Reindl Harald

10:40 a.m.

Am 26.06.2015 um 10:09 schrieb Roberto Spadim:

...

the problem is, i don't know whats the index of order by without knowing the sql, the 'create' table and the optimizer choise/options but i want that optimizer avoid the order by index with a optimizer_switch configuration or a sql hint i don't know if it's possible, i don't want to add a FROM table IGNORE INDEX (some index), i want something like ORDER BY IGNORE INDEXES some_columns

but you won't get that because you *have to know* your tables and queries - "IGNORE INDEXES some_columns" would be by all respect pure stupidity because *everybody* excepts here to list index names P.S.: there is not need to "reply all" on a mailing-list and so sent duplicates to all the people you list additionally as CC

...

2015-06-26 4:42 GMT-03:00 Justin Swanhart mailto:greenlion@gmail.com>:

The ignore index hint. That will ignore the index

Justin Swanhart

3:38 p.m.

Should it be worked around in hacky way or should someone actually invest in fixing the terribly broken optimizer? Sent from my iPhone

...

On Jun 26, 2015, at 1:40 AM, Reindl Harald wrote:

...
Am 26.06.2015 um 10:09 schrieb Roberto Spadim: the problem is, i don't know whats the index of order by without knowing the sql, the 'create' table and the optimizer choise/options but i want that optimizer avoid the order by index with a optimizer_switch configuration or a sql hint i don't know if it's possible, i don't want to add a FROM table IGNORE INDEX (some index), i want something like ORDER BY IGNORE INDEXES some_columns

but you won't get that because you *have to know* your tables and queries - "IGNORE INDEXES some_columns" would be by all respect pure stupidity because *everybody* excepts here to list index names

P.S.: there is not need to "reply all" on a mailing-list and so sent duplicates to all the people you list additionally as CC

...
2015-06-26 4:42 GMT-03:00 Justin Swanhart mailto:greenlion@gmail.com>:

The ignore index hint. That will ignore the index

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Roberto Spadim

23 Jun 23 Jun

4:42 a.m.

at insert, update and replace 2015-06-22 23:39 GMT-03:00 Justin Swanhart :

...

Hi,

If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that.

--Justin

On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim wrote:

...
and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select

in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong?

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

Justin Swanhart

4:43 a.m.

You check uniqueness on the PK. don't make a functionally dependent unique index and you will check uniqueness only once. On Mon, Jun 22, 2015 at 7:42 PM, Roberto Spadim wrote:

...

at insert, update and replace

2015-06-22 23:39 GMT-03:00 Justin Swanhart :

...
Hi,

If you do a code inspection you will find that uniqueness is checked on insertion, and the database cares not about it after that.

--Justin

On Mon, Jun 22, 2015 at 7:20 PM, Roberto Spadim wrote:

...
and there's optimization with this flag? i see that equal could be optimized cause if we found 1 row we can return to user that row and stop select

in other words, if i see a index with primary key or unique columns, could i change the index to unique and get some kind of performace? today i think not ,cause unique key check if it's really unique (for example at insert/update/replace), i'm wrong?

-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle

3318

Age (days ago)

3322

Last active (days ago)

List overview

Download

31 comments

4 participants

participants (4)

Federico Razzoli
Justin Swanhart
Reindl Harald
Roberto Spadim

[Maria-discuss] doubt about index

tags

participants (4)