List:Cluster« Previous MessageNext Message »
From:David Ennis Date:October 30 2010 7:08am
Subject:Re: Bad performance on SELECT's using NDB
View as plain text  
Thanks.

From the thread, I thought the simple removal of the quotes was what
tricked it to using a different index and that you didnt need index hints
for the proper results.  That's what confused me.

Regards,
David



> Dave,
>
> nope. I really don't know what removing quotes actually did, (explain
> was still showing that a primary key is being used), but query execution
> time lowered from about 7 sec to around 1.5. Don't ask me why, I've no
> clue :)
> To have the situation in which correct index was being used I needed to
> use "force index".
> Quite odd thing is that EXPLAIN (while using PK) was showing that only
> one row was scanned while in fact there were a lot more.
>
> Regards,
> Slawek
>
> W dniu 2010-10-29 12:58, David Ennis pisze:
>> HI.
>>
>> A question:
>>
>> If I understand the thread correctly, removing the quotes tricked the
>> optimizer into using another index.
>>
>> Could you have also just used an iindex hint (FORCE INDEX or USE INDEX)?
>>
>> Just wondering if using the SQL syntax for index hints would be better
>> than tricking the optimizer.  Would there be a drawback to this approach
>> -
>> other than the obvious of taking the decision out of the hands of the
>> optimizer?
>>
>> Thanks,
>> David
>>
>>
>>> Hi,
>>>
>>> The optimizer gets fooled by the statistics it receives from MySQL
>>> Cluster.
>>> It thinks that it needs to look through 10 records in an index search,
>>> for any index.
>>>
>>> Thus if you have two similar indexes  (a - integer, ts - timestamp):
>>> index_a ( a)
>>> index_b (a, ts )
>>> index_c (b)
>>>
>>> And do SELECT * FROM x WHERE a=1 and ts = now();
>>>
>>> then chances are that the index_a will be used (and as a side effect 1M
>>> rows are returned) where as if index_b was used, then 2 records would
>>> have been returned.
>>>
>>> Of course it will not pick the wrong index ( index_b), but if similar
>>> indexes exists it can happen, as you have noticed when you have ID
>>> (primary key), and then really a better condition on another index.
>>>
>>> When you test your queries you should enable the slow query log and
>>> have
>>> long_query_timeout=0.1 or something, so you can catch queries using the
>>> wrong indexes.
>>>
>>> i hope proper index stats will be fixed.
>>>
>>> BR
>>> johan
>>>
>>> Slawomir Smirnow wrote:
>>>> Hi.
>>>>
>>>> Yup, with Johan's advice to force a different index, query executes in
>>>> a twinkling.
>>>>
>>>> But, with this in mind, one next thing bothers me... as I see NDB is
>>>> *always* trying to use PRIMARY key, correct ? I'm trying to move on
>>>> NDB already prepared application (with about 2k tx / sec on innodb),
>>>> that was previously reviewed and indexed by me. In 99% cases simply
>>>> creation of an index(es) did the trick, and optimizer was using the
>>>> new ones, without the need to force/use of a particular index. Do I
>>>> have to review all of the queries and force indexes, rewrite queries,
>>>> and so where needed ? or is there another way to, well, I don't know,
>>>> change optimizer behaviour ?
>>>>
>>>> Thanks in advance.
>>>>
>>>> Regards,
>>>> Slawek
>>>>
>>>> W dniu 2010-10-29 13:04, Johan Andersson pisze:
>>>>> hi,
>>>>>
>>>>> The main problem in this case is that the wrong index was used
>>>>> (PRIMARY index was on the ID), whereas an existing index on PLAYERID
>>>>> should have been used. The optimizer picked the wrong index in this
>>>>> query.
>>>>>
>>>>> Slawomir shared the table def with me, so a more complete analysis
>>>>> showed that this was the main problem.
>>>>>
>>>>> BR
>>>>> johan
>>>>>
>>>>> Chris Picton wrote:
>>>>>> Hi Johan
>>>>>>
>>>>>> I have two question based off your reply
>>>>>>
>>>>>> 1. Why do you expect that that would make a difference?
>>>>>> 2. How could I check that a query would behave like this - is
> there
>>>>>> any
>>>>>> form of 'explain' which would indicate that a comparison is
> being
>>>>>> done
>>>>>> inefficiently?
>>>>>>
>>>>>> Thanks
>>>>>> Chris
>>>>>>
>>>>>> On Fri, 2010-10-29 at 11:58 +0200, Johan Andersson wrote:
>>>>>>> Hi,
>>>>>>> Try to remove the quotes  around PLAYERID='80009' so it
> becomes:
>>>>>>> PLAYERID=80009
>>>>>>>
>>>>>>> BR
>>>>>>> johan
>>>>>>>
>>>>>>> Slawomir Smirnow wrote:
>>>>>>>> Hi.
>>>>>>>>
>>>>>>>> I'm trying to configure and run MySQL Cluster.
>>>>>>>>
>>>>>>>> Currently I have finished importing data (that was
> previously
>>>>>>>> stored in Inno tables).
>>>>>>>> Unfortunately, after runing a few tests the performance
> of some
>>>>>>>> queries is rather poor.
>>>>>>>>
>>>>>>>> I.e:
>>>>>>>> Table with 655k rows (count(*) on it went smoothly)
>>>>>>>>
>>>>>>>> SELECT GraphicalCommunication.ID,
>>>>>>>> GraphicalCommunication.MESSAGETYPE,
>>>>>>>> GraphicalCommunication.PLAYERID,
> GraphicalCommunication.DATE,
>>>>>>>> GraphicalCommunication.DATA,
> GraphicalCommunication.READ,
>>>>>>>> GraphicalCommunication.ACTION,
> GraphicalCommunication.DISPLAY,
>>>>>>>> GraphicalCommunication.POPUP FROM
> `GraphicalCommunication` WHERE
>>>>>>>> GraphicalCommunication.PLAYERID='80009' AND
>>>>>>>> GraphicalCommunication.ID<2845296 AND
>>>>>>>> GraphicalCommunication.MESSAGETYPE=6 AND
>>>>>>>> GraphicalCommunication.READ=0 AND
> GraphicalCommunication.DISPLAY=1
>>>>>>>> ORDER BY GraphicalCommunication.ID DESC LIMIT 1;
>>>>>>>>
>>>>>>>> Empty set (7.56 sec)
>>>>>>>>
>>>>>>>> Anyway, explain shows that a primary key is being used,
> so in my
>>>>>>>> opinion it shouldn't take such amount of time, right ?
>>>>>>>>
> +----+-------------+------------------------+-------+---------------------------------+---------+---------+------+------+-------------+
>>>>>>>>
>>>>>>>> | id | select_type | table                  | type  |
>>>>>>>> possible_keys                   | key     | key_len | ref
>  | rows
>>>>>>>> | Extra       |
>>>>>>>>
> +----+-------------+------------------------+-------+---------------------------------+---------+---------+------+------+-------------+
>>>>>>>>
>>>>>>>> |  1 | SIMPLE      | GraphicalCommunication | range |
>>>>>>>> PRIMARY,integrity_1,integrity_2 | PRIMARY | 4       |
> NULL |    1
>>>>>>>> | Using where |
>>>>>>>>
> +----+-------------+------------------------+-------+---------------------------------+---------+---------+------+------+-------------+
>>>>>>>>
>>>>>>>> 1 row in set (0.00 sec)
>>>>>>>>
>>>>>>>>
>>>>>>>> On InnoDB the same query is executed in a split
> second...
>>>>>>>>
>>>>>>>> What could possibly be wrong ?
>>>>>>>>
>>>>>>>> Thanks in advance.
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>> Slawek
>>>>>>>>
>>>>>>>>
>>>>
>>>
>>> --
>>> MySQL Cluster Mailing List
>>> For list archives: http://lists.mysql.com/cluster
>>> To unsubscribe:
>>> http://lists.mysql.com/cluster?unsub=1
>>>
>>>
>>> --
>>> This message has been scanned for viruses and
>>> dangerous content by MailScanner, and is
>>> believed to be clean.
>>>
>>>
>>
>
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster?unsub=1
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>
>


-- 
---------------------------
David Ennis
Sféar
The Square
Oughterrard
CO Galway
Ireland
+353 91 39 57 17
Thread
Bad performance on SELECT's using NDBSlawomir Smirnow29 Oct
  • Re: Bad performance on SELECT's using NDBJohan Andersson29 Oct
    • Re: Bad performance on SELECT's using NDBChris Picton29 Oct
      • Re: Bad performance on SELECT's using NDBJohan Andersson29 Oct
        • Re: Bad performance on SELECT's using NDBSlawomir Smirnow29 Oct
          • Re: Bad performance on SELECT's using NDBJohan Andersson29 Oct
            • Re: Bad performance on SELECT's using NDBDavid Ennis29 Oct
              • Re: Bad performance on SELECT's using NDBSlawomir Smirnow29 Oct
                • Re: Bad performance on SELECT's using NDBDavid Ennis30 Oct
    • Re: Bad performance on SELECT's using NDBSlawomir Smirnow29 Oct