All the "SHOW FULL COLUMN" queries that we do on the respective tables
are very small tables. They hardly cross 50 rows. Hence that is the
reason whenever these queries are made i can see high cpu usage in
%user_time. If it were very large tables then the cpu would be spending
lot of time in waiting for I/O as the databases reads would have to do a
lot of I/O to read the data from the disks.
On 6/2/2014 8:13 PM, Michael Dykman wrote:
> 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
>>>> 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.
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql