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