List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:June 2 2014 2:43pm
Subject:Re: SHOW FULL COLUMNS QUERIES hogging my CPU
View as plain text  
The advice to 'avoid LIKE in general' is a little strong.  LIKE is
very useful and does not always cause inefficient queries, although
the possibility is there.

However, there is one form which must be avoided at all costs: the one
where the glob-text matcher is the first character in that string.
"LIKE '%' " or "LIKE '%foobar' " are both equally costly as, no matter
what index there might be on that column, the query will have to visit
every single row to test the match, therefore inducing a full table
scan.  putting it early in the expression is equally dangerous, but
how dangerous depends on how much data you have:  "LIKE 'a%'" avoids
visiting every row but it still has to test against a significant
subset of all rows:  If you have 100 million rows, this will still
cause your query to visit a very large number of them.

So, I would have to ask: how many records are in that table? How many
columns?  is it a table or a view?

On Mon, Jun 2, 2014 at 10:21 AM, Jatin Davey <jashokda@stripped> wrote:
> On 6/2/2014 7:18 PM, Reindl Harald wrote:
>>
>>
>> Am 02.06.2014 15:35, schrieb Jatin Davey:
>>>
>>> I am no expert with mysql and databases. Hence seeking out some help on
>>> this forum.
>>>
>>> Basically i got a query dump of my application during its operation. I
>>> had collected the queries for about 4 hours.
>>> Ran some scripts on the number of queries being sent to the databases.
>>>
>>> The query file was a whopping 4 GB is size. Upon analyzing the queries i
>>> found that there were a total of 30
>>> million queries made to the Database out of which 10 million queries were
>>> only doing "SHOW FULL COLUMN" queries.
>>>
>>> The SHOW FULL COLUMN queries were of the format as below:
>>>
>>> SHOW FULL COLUMNS FROM `APIC_PHY_FABRIC_PHYSICAL_INTERFACE` FROM
>>> `db_private_admin` LIKE '%'
>>>
>>> This is causing considerable cpu usage in %user_time in my system
>>
>> fix your application - there is no single reason to run such
>> queries 10 million times because the result won't change all
>> the time
>>
>> and avoid like in general
>
>
> Our application does not send such queries to the DB. I have searched
> through my entire code and we dont run such queries. It has something to do
> with a layer below our application. But i am not sure as to where it is.
>
> Thanks
> Jatin
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
Thread
SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
  • Re: SHOW FULL COLUMNS QUERIES hogging my CPUReindl Harald2 Jun 2014
    • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
      • Re: SHOW FULL COLUMNS QUERIES hogging my CPUMichael Dykman2 Jun 2014
        • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
      • Re: SHOW FULL COLUMNS QUERIES hogging my CPUSinger Wang2 Jun 2014
        • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
          • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey2 Jun 2014
            • Re: SHOW FULL COLUMNS QUERIES hogging my CPUMorgan Tocker2 Jun 2014
              • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey3 Jun 2014
          • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJohan De Meersman2 Jun 2014
            • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJohan De Meersman3 Jun 2014
              • Re: SHOW FULL COLUMNS QUERIES hogging my CPUJatin Davey3 Jun 2014