maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

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@xxxxxxxxxxxxx>
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@xxxxxxxxx>:
>
>> 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@xxxxxxxxx>
>> wrote:
>>
>>> No, you have to look in the leaf!
>>>
>>> On Mon, Jun 22, 2015 at 7:43 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
>>> 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@xxxxxxxxx>:
>>>>
>>>>> 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@xxxxxxxxx>
>>>>> 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@xxxxxxxxxxxxx> 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
>