[Maria-discuss] doubt about index
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
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?
2015-06-22 19:47 GMT-03:00 Reindl Harald 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,...)
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
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 roberto@spadim.com.br ha scritto:
Oggetto: Re: [Maria-discuss] doubt about index A: "Reindl Harald" h.reindl@thelounge.net Cc: "Maria Discuss" maria-discuss@lists.launchpad.net Data: Martedì 23 giugno 2015, 00:53
2015-06-22 19:47 GMT-03:00 Reindl Harald 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 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
Am 23.06.2015 um 00:53 schrieb Roberto Spadim:
2015-06-22 19:47 GMT-03:00 Reindl Harald <h.reindl@thelounge.net 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
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 h.reindl@thelounge.net ha scritto:
Oggetto: Re: [Maria-discuss] doubt about index A: "Maria Discuss" maria-discuss@lists.launchpad.net 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@thelounge.net
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
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@thelounge.net ha scritto:
Oggetto: Re: [Maria-discuss] doubt about index A: "Maria Discuss" maria-discuss@lists.launchpad.net 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@thelounge.net
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
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@thelounge.net 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:
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@thelounge.net
ha scritto:
Oggetto: Re: [Maria-discuss]
doubt about index
A:
"Maria Discuss" maria-discuss@lists.launchpad.net
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@thelounge.net
> 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
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@spadim.com.br wrote:
2015-06-22 19:47 GMT-03:00 Reindl Harald 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,...)
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
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@gmail.com:
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@spadim.com.br wrote:
2015-06-22 19:47 GMT-03:00 Reindl Harald 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,...)
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
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@spadim.com.br 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@gmail.com:
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@spadim.com.br wrote:
2015-06-22 19:47 GMT-03:00 Reindl Harald 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,...)
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
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@gmail.com 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@spadim.com.br 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@gmail.com:
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@spadim.com.br wrote:
2015-06-22 19:47 GMT-03:00 Reindl Harald 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,...)
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
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?
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?
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?
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 greenlion@gmail.com:
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?
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 greenlion@gmail.com:
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
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 greenlion@gmail.com:
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
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 greenlion@gmail.com:
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 greenlion@gmail.com:
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
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 greenlion@gmail.com:
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 greenlion@gmail.com:
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
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 greenlion@gmail.com:
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 greenlion@gmail.com:
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 greenlion@gmail.com:
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
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 roberto@spadim.com.br:
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 greenlion@gmail.com:
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 greenlion@gmail.com:
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 greenlion@gmail.com:
> 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
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 roberto@spadim.com.br 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 roberto@spadim.com.br:
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 greenlion@gmail.com:
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 greenlion@gmail.com:
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 greenlion@gmail.com: > >> 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
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 greenlion@gmail.com:
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 roberto@spadim.com.br 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 roberto@spadim.com.br:
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 greenlion@gmail.com:
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 greenlion@gmail.com:
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 greenlion@gmail.com: >> >>> 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
Ignore index
Sent from my iPhone
On Jun 25, 2015, at 7:44 PM, Roberto Spadim roberto@spadim.com.br 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 greenlion@gmail.com:
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 roberto@spadim.com.br 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 roberto@spadim.com.br:
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 greenlion@gmail.com:
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 greenlion@gmail.com: > 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 greenlion@gmail.com: >>>> 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
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 greenlion@gmail.com:
Ignore index
Sent from my iPhone
On Jun 25, 2015, at 7:44 PM, Roberto Spadim roberto@spadim.com.br 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 greenlion@gmail.com:
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 roberto@spadim.com.br 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 roberto@spadim.com.br:
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 greenlion@gmail.com:
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 greenlion@gmail.com:
> 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 greenlion@gmail.com: >>> >>>> 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
The ignore index hint. That will ignore the index.
Sent from my iPhone
On Jun 25, 2015, at 10:46 PM, Roberto Spadim roberto@spadim.com.br 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 greenlion@gmail.com:
Ignore index
Sent from my iPhone
On Jun 25, 2015, at 7:44 PM, Roberto Spadim roberto@spadim.com.br 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 greenlion@gmail.com:
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 roberto@spadim.com.br 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 roberto@spadim.com.br:
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 greenlion@gmail.com: > 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 greenlion@gmail.com: >>> 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 greenlion@gmail.com: >>>>>> 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
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 greenlion@gmail.com:
The ignore index hint. That will ignore the index.
Sent from my iPhone
On Jun 25, 2015, at 10:46 PM, Roberto Spadim roberto@spadim.com.br 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 greenlion@gmail.com:
Ignore index
Sent from my iPhone
On Jun 25, 2015, at 7:44 PM, Roberto Spadim roberto@spadim.com.br 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 greenlion@gmail.com:
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 roberto@spadim.com.br 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 roberto@spadim.com.br:
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 greenlion@gmail.com:
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 greenlion@gmail.com: > >> 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 greenlion@gmail.com: >>>> >>>>> 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
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 <greenlion@gmail.com mailto:greenlion@gmail.com>:
The ignore index hint. That will ignore the index
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 h.reindl@thelounge.net 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 <greenlion@gmail.com 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
at insert, update and replace
2015-06-22 23:39 GMT-03:00 Justin Swanhart greenlion@gmail.com:
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?
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 roberto@spadim.com.br wrote:
at insert, update and replace
2015-06-22 23:39 GMT-03:00 Justin Swanhart greenlion@gmail.com:
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
participants (4)
-
Federico Razzoli -
Justin Swanhart -
Reindl Harald -
Roberto Spadim