Searching 3,807,533 rows - php

Hello,
I'm doing this to search a db with 3,807,533 rows that's over 2.8GB in size


SELECT * FROM `titles` WHERE (title LIKE '%$search%') ORDER BY `id` DESC LIMIT 30

when doing so it takes about 20 seconds to search

server specs:

duel core Xenon 2.8Ghz
2GB ddr3 ram


how can I speed this way up?

Thanks,
Bix

Here are 32 ways of speeding up queries - http://www.ajaxline.com/32-tips-to-speed-up-your-mysql-queries

What comes to mind first - do you use indexes on 'id'?

What you will need to do if you are using MySQL is to change the data type for 'title' column to text and then use full text search instead of the 'like' clause

The reason is the 'like' does not use index and is doing a full table scan, thus, you need to replace it with full text search that uses a full text index.

For other database, the principle is the same, use full text, just that it's being used differently.

Hope this helps.

that database with 3millions rows of data could really use a few seconds to load and search.
please don't get me wrong.

maybe this would help -- http://stackoverflow.com/questions/...ect-like-queries-in-mysql-on-multiple-columns

Goodluck.