maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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

> The ignore index hint.  That will ignore the index.
>
> Sent from my iPhone
>
> On Jun 25, 2015, at 10:46 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
> 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@xxxxxxxxx>:
>
>> Ignore index
>>
>> Sent from my iPhone
>>
>> On Jun 25, 2015, at 7:44 PM, Roberto Spadim <roberto@xxxxxxxxxxxxx>
>> 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@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
>>
>>
>
>
> --
> Roberto Spadim
> SPAEmpresarial - Software ERP
> Eng. Automação e Controle
>
>


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