Search Query way to slooooow ...

The code below takes about 3 min to run on a 110K db ...

Any idea how to speed it up ? I guess it the LIKE '%$keyword%'
thing that is the problem ...

The code uses a keyword to search and find a random id each time.
And I need it to be *fast* ! Anyone with a solution pls answer here and also pm me !

---

$sql = "
FROM articles
WHERE title = '%$keyword%' ";

$MySQL = "
SELECT id ".
$sql."
LIMIT ".rand(0,$db->get_var("
SELECT count(*)".
$sql."
")-1).",1";

$rand = $db->get_row($MySQL);

 

It is confusing.
Can you give us the exact query instead of the string where you are concatenating several pieces?
link dev

 

Okay, this is a sample MySQL query:

SELECT id FROM articles WHERE title LIKE '%pets%' LIMIT 68,1

I just ran this one and it took almost 3 min on my 110K db ...
Way tooo looong time ... Any ideas how to speed it up ???

 

Do you have an index on field title?
Did you check it with explain?

EXPLAIN SELECT id FROM articles WHERE title LIKE '%pets%' LIMIT 68,1

 

Yes, I have title indexed ... Didn't do the EXPLAIN thing ... Will do it now...

 

The title field is a varchar(255)
and the title_index is on title, 100

I guess this means the first 100 char in the title is indexed?
If I change it from 100 to 255, would it help ?

 

Okay, I tested a little more ... If I remove first % and run this query:

SELECT id FROM articles WHERE title LIKE 'pets%' LIMIT 68,1
It finish in about 3 sec ...

But the original query with '%pets%' it still takes about 3 min:

SELECT id FROM articles WHERE title LIKE '%pets%' LIMIT 68,1

And the thing is ... I need the '%pets%' thing to work, and work faster.
Any idea guys ?

 

When you search with both side wildcard it has to check all records.
So Index becomes useless.
Maybe fulltext index may help

 

Try re-creating/re-indexing the index on title. You may done too many inserts/updates since you last created the index.

drop/create index should also work.
--link dev

 

I'm playing with the index now to see if I can get anything to work faster.
If, then I'll let u know

 

So far my index experiment didn't increase the speed

Does any of you know a better way to write a query for what I want/need?
i.e. I want to get a RANDOM post from the db that also has a match to a keyword ...

In my case, for example, if I enter "pets" the query should find a random post from the db that has "pets" somewhere in the title.

If it is possible to do this in any other (and faster) way than what I tried already ... Let me know ... Meanwhile I'll continue my struggle ;-)

 

I got it to temporary work almost okay, but still, if anyone come up with a nice query pls let me know ...

 

Like Clark Kent said, if you search on %keyword it has little use of the index..
An index is made on the first characters of a word, so only searches like keyword% will use it and be fast.

If you need to search on words used in sentences, instead of storing the whole sentence, store keywords (with an index that links back to the full sentence records where it came from).

 

Hi Jona,

Just in case, have you experimented with the fulltext search syntax?

The downside is that the table needs to by stored as MyISAM and it only works for char, varchar, text columns - this may be a problem if the table has frequent updates.

The syntax for a fulltext search is:

SELECT id FROM articles WHERE MATCH (title) AGAINST ('pets');

It also returns a relevance score for each result and will also return no results for too popular matches e.g. MATCH (title) AGAINST ('the') might return too many rows.

Hope this helps.