mysql query gives 500 error while running in loops

Question

I have made a query in which i have to find keywords from comments. I have more than 4 millions keywords. Special character can be comes after/before any Keyword in comments so I have to make more than 50 patterns to find the keywords. Here is the query :

[SELECT DISTINCT a.keywords,a.productID,oproduct_name 
FROM review1_filter_keyword_comment as a where ("16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('% ', LCASE(a.keywords), ' %') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('% ', LCASE(a.keywords), '') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('', LCASE(a.keywords), ' %') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('', LCASE(a.keywords), '') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('% ', LCASE(a.keywords), '.%') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%/', LCASE(a.keywords), '.%') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%,', LCASE(a.keywords), '.%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%:', LCASE(a.keywords), '.%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%-', LCASE(a.keywords), '.%') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('', LCASE(a.keywords), '.%') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('', LCASE(a.keywords), '-%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%-', LCASE(a.keywords), ' %') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%-', LCASE(a.keywords), '') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('% ', LCASE(a.keywords), '-%') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%(', LCASE(a.keywords), '-%') OR "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%-', LCASE(a.keywords), ')%') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%/', LCASE(a.keywords), '-%') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%-', LCASE(a.keywords), '/%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%-', LCASE(a.keywords), ',%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%,', LCASE(a.keywords), '-%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%:', LCASE(a.keywords), '-%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%-', LCASE(a.keywords), ':%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%-', LCASE(a.keywords), '') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%(', LCASE(a.keywords), ' %') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('% ', LCASE(a.keywords), ')%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%(', LCASE(a.keywords), ')%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%(', LCASE(a.keywords), '/%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%/', LCASE(a.keywords), ')%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%(', LCASE(a.keywords), ',%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%,', LCASE(a.keywords), ')%') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%(', LCASE(a.keywords), ':%') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%:', LCASE(a.keywords), ')%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('% ', LCASE(a.keywords), '/%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('', LCASE(a.keywords), '/%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%/', LCASE(a.keywords), '') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%/', LCASE(a.keywords), '/%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%/', LCASE(a.keywords), ',%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%,', LCASE(a.keywords), '/%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%/', LCASE(a.keywords), ':%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%:', LCASE(a.keywords), '/%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('% ', LCASE(a.keywords), ',%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%,', LCASE(a.keywords), '') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('', LCASE(a.keywords), ',%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%:', LCASE(a.keywords), ',%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%,', LCASE(a.keywords), ',%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%,', LCASE(a.keywords), ':%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('', LCASE(a.keywords), ':%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%:', LCASE(a.keywords), ':%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('% ', LCASE(a.keywords), ':%') OR 
"16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%:', LCASE(a.keywords), ' %') OR
 "16540221 AM-OH-AGC-UP64050-2-1" LIKE CONCAT('%:', LCASE(a.keywords), '')) and 
 a.keywords<>'' and a.keywords IS NOT NULL and LENGTH(a.keywords)>2 and 
isActive='1' and productID<>'' and productID IS NOT NULL order by keywords][1]

When i run this query single time it takes 6021 ms. But if I run multiple times like more than 50 it gets crash and shows 500 Internal server error (link: enter image description here). I know my query takes very long time to run due to this the error occurs. So i want to know is there any way to optimize my query.

Thank you

PHP - although it is, probably, the most easy to learn also uses the most CPU usage. I'd suggest you using something like c++ or c# to do this. You can also look at Facebooks `Hack` programming language which simply compiles `PHP` to `C++` reducing the CPU usage by 38% which should optmise this query. — Jaquarh, Mar 29 '16 at 10:56
Have you considered not using mysql but e.g. solr, elisticsearch et al for this? — VolkerK, Mar 29 '16 at 10:57
Add `set_time_limit(0);` at the top of the script and then execute. — Abdul Mannan, Mar 29 '16 at 10:58
You can use steps in your loops by not fire the whole loop and query at once — node_modules, Mar 29 '16 at 10:58
your problem is database design. If you can change it, then post another question under mysql tag alone, asking for a proper database design for your case. — Your Common Sense, Mar 29 '16 at 11:01
@KyleE4K could you provide me any example or tutorial regarding this? — Manish, Mar 29 '16 at 11:02
@MaTharBeevi **if you care to look at the query** you will see that no index could be ever used for it. — Your Common Sense, Mar 29 '16 at 11:02
And kind advice, pls avoid or condition queries. Hacker easy hacked ur db — MaThar Beevi, Mar 29 '16 at 11:03
@MaTharBeevi I have used simple and ful text both. but no luck — Manish, Mar 29 '16 at 11:04
`C#` and `C++` are more `strongly typed` languages, you'll need to do your research if you choose to use this method. However, if you want to switch to `Hack`. Facebook uses `gitHub` to provide its compiler, you simply follow there documentation. https://docs.hhvm.com/ - NOTE: You need Linux to use HVVM. — Jaquarh, Mar 29 '16 at 11:05

mysql query gives 500 error while running in loops

0 Answers0