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.
Thanks
Jatin
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 <jasho...@cisco.com> 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
