maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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

> 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@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
>>>
>>
>>
>
>
> --
> Roberto Spadim
> SPAEmpresarial - Software ERP
> Eng. Automação e Controle
>



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