mysql overload...

[kunal]

hey guys.. im kinda confused now... I have a mysql table which has over 90,000 recods... I have the following mysql query :

SELECT descr, link, ddate from links WHERE (descr like %bored%) ORDER BY ddate DESC LIMIT $offset, 100;

now, the above query causes mysql load to sky rocket to 90%... but if i try the following :

SELECT descr, link, ddate from links WHERE (descr like %bored%) LIMIT $offset, 100;

it remains static between 15-20....

the table is indexed on ddate.. both link, and descr are varchar(255) character columns.. and ddate is int(20) column storing a unix time stamp...

any ideas on how i can get the order by to function without overloading mysql?

thanks
kunal

[ffeingol]

Well a couple of things. You can put "describe" in fron the the SQL and MySQL will tell you how it's processig the query. That might give you some indication of what's going on.

Second, the only way MySQL can process "%bored%" is to scan the entire table. That is going to be a slow process.

The order by will cause MySQL to sort the data. It seems quite strange that sorting at most 100 rows is killing MySQL.

Frank

[freakysid]

Frank you give sound advice as always, and I am no expert in these matters. However, I understand that the problem is that in order to return the 100 results, mysql must first order the entire result set.

Creating an index on ddate AND descr may help.

[kunal]

hey...
i tried using both, describe and explain... both showed the worst thing, filesort

i tried making both ddate, and descr indexs.. dint work.. i tried making each an index, dint work either... the load just sky rocketed...

come to think of it.. should limit reduce the result set? isnt that the whole point of limit?

[MattR]

kunal as I posed at DevShed, MySQL cannot use an index if you have '%$str%' vs '$str%'.

[DR_LaRRY_PEpPeR]

Originally posted by kunal
i tried using both, describe and explain... both showed the worst thing, filesort

when you do EXPLAIN, "Using filesort" isn't that bad. lots of queries have that. the thing that's bad, that is on yours, is the "Type" column -- it probably says "ALL." that means MySQL is doing a full table scan (read: no index being used). not good. but when you have a LIKE that starts with a wildcard, MySQL can't use an index, like MattR said.

[BlackCatt]

Okay, I by no means an a MySQL guru but, the situation here seems that the following is happening.

When you do a wildcard search "where descr like %bored%" with a % in front MySQL HAS to do a full table scan. So the index won't be used. Someone stated this earlier.

The ORDER BY compounds this problem because after the entire table scan. It has to take all of the qualifying rows (everything that matches the where clause) and then sort them after the table scan.

If you can get rid of the wildcard in front of the LIKE clause that would help immensly. Also, I believe MySQL's newest version supports full-text indexing. You may want to look into that. It would speed things up quite a bit as well.

Hope this helps.

BlackCatt