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 > -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
