i need help sql

[softpk]

My sql suspended because it solved the problem


This error show has been godaddy hosting provider

SELECT  SQL_NO_CACHE COUNT(*) AS count FROM dle_post WHERE dle_post.approve AND  dle_post.date < '2012-03-27 23:01:57' AND (short_story LIKE  '%pro%tools%9%' OR full_story LIKE '%pro%tools%9%' OR dle_post.xfields  LIKE '%pro%tools%9%' OR title LIKE '%pro%tools%9%')

EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE dle_post ALL date    95693 Using where

This query examines 95693 rows, 

please help me

softpk Reviewed by softpk on 28th Mar 2012. i need help sql My sql suspended because it solved the problem This error show has been godaddy hosting provider SELECT SQL_NO_CACHE COUNT(*) AS count FROM dle_post WHERE dle_post.approve AND dle_post.date < '2012-03-27 23:01:57' AND (short_story LIKE '%pro%tools%9%' OR full_story LIKE '%pro%tools%9%' OR dle_post.xfields LIKE '%pro%tools%9%' OR title LIKE '%pro%tools%9%') EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE dle_post ALL date 95693 Using where Rating: 5

[Lock Down]

Obviously this query was taking too long and caused mysql to time out with all those like statements against 95K records.

I don't use DLE so I can't comment on the structure.

If this is some type of plugin than it needs to be rewritten and maybe add fulltext indices if there aren't any for those fields.

[soft2050]

By default, unless you did any changes in DLE, all those 4 fields are fulltext. You might want to add total limit to search for maybe.

SELECT  SQL_NO_CACHE COUNT(*) AS count FROM dle_post WHERE dle_post.approve AND  dle_post.date < '2012-03-27 23:01:57' AND (short_story LIKE  '%pro%tools%9%' OR full_story LIKE '%pro%tools%9%' OR dle_post.xfields  LIKE '%pro%tools%9%' OR title LIKE '%pro%tools%9%') LIMIT 100