maria-discuss team mailing list archive

[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