0

I have a table having 200K rows. When I execute a query it's taking too much time; approximately 2 minutes.

This is my query:

SELECT a,b,c,d,@row:="tag1" as tag 
  FROM tableName
 WHERE tagName like "%helloworld|%"
 ORDER BY viewCount desc
  LIMIT 20;

helloworld| occurred only in 2 rows.

I want to change the query so if the data is present more than 20 times, it should return 20 rows else whatever rows present.

How to optimize this query?

Community
  • 1
  • 1
vikash5472
  • 200
  • 2
  • 16
  • 1
    That runs a full table scan. Try fulltext search instead – juergen d Jul 14 '16 at 11:20
  • 1
    You need to create separate table with tags to have ability of filtering without fullscan. – vp_arth Jul 14 '16 at 11:23
  • 1
    Try fulltext index on `tagName` column together with `MATCH AGAINST`, i.e.: `WHERE MATCH(tagName) AGAINST('helloworld|' IN BOOLEAN MODE)` – mitkosoft Jul 14 '16 at 11:23
  • Read this -- sargable queries are queries that can exploit indexes. Yours cannot. http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable – O. Jones Jul 14 '16 at 11:29
  • 2
    Unless your rows are very wide or you are on a very slow computer, scanning 200,000 rows should take seconds, not minutes. – Gordon Linoff Jul 14 '16 at 11:32

2 Answers2

1

You cannot speed this up as written.

The WHERE clause with the LIKE requires that you scan each and every row. It's O(N), where N = # of rows in the table. It will run more slowly as your table size increases.

You can make the query run faster if you can find a way to parse that string into tokens that you can INSERT as columns and index.

duffymo
  • 305,152
  • 44
  • 369
  • 561
-1

Try these

  1. Set index on your search field of your table & then check the query execution time
  2. Not sure what's viewCount here but i guess you are getting this with subquery try to remove order statement & then check the query execution time
Virender
  • 168
  • 10