Results 1 to 2 of 2
Thread: Slow SQL search
Jun 26, 2004, 01:14 #1
- Join Date
- Mar 2003
- Brisbane, Australia
- 0 Post(s)
- 0 Thread(s)
Slow SQL search
I have a MySQL database of archived punchlines (I run a caption contest website) that's starting to get bloated (there's now 90,000 punchlines listed). I have a search page to look through archived punchlines and often the search script takes ages to execute. Eg - the page is blank for 10 to 30 seconds then the contents finally load. I'm just using a simple search query such as "SELECT punchline, username FROM punchlines WHERE punchline LIKE '%$searchterm%'".
So my question - is there any techniques I can try to make things quicker and more efficient or does having a database of that size lead inevitably to slow execution times?Tips 'n' Tutorials
Jun 28, 2004, 02:48 #2
your query can't use any index because of the % in front of $searchterm, so it has to do a full table scan, i.e. looking through all 90.000+ lines which may last some time Take a look at the fulltext index feature, maybe that's what you are looking for:
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.htmlNever ascribe to malice,
that which can be explained by incompetence.
Your code should not look unmaintainable, just be that way.