maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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

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


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