Is my problem that of needing an index?

[Jeffro]

I've been writing a php app and my mysql knowledge isn't too keen, I will confess.  I have a single table with 5 columns and that's it...  and when I'm browsing through the app in my browser, it loads very fast... most of the time.  But, it seems like every 10th click (on average) will see my page just churn and churn for well over a minute and then finally resolve to the page it should.  I do have an index on the date column but I'm sure there's more to it than just clicking the feature to add an index. 

 

The query is pretty basic but the table is good size at over 18,000 records.  Here's the query that I typically invoke:

SELECT * FROM mytable WHERE myurl LIKE '%/$category/%' ORDER BY date DESC LIMIT 10

 

1)  If the query is executing within a second or 2 most of the time, but then taking a good minute every so often, is it still possible that indexing is the problem or should I consider something else as the problem? 

2)  Is there a 'mysql for dummies' approach that I could take in resolving this?  I've tried googling but haven't found anything that's helping me due to my limited knowledge of mysql. 

 

Thanks for any tips. 

[requinix]

If you're doing this category search often then you should have a database table to support it: one relating whatever URLs to a category name (or ID). Then you can do an exact search on the category table.

[awpti]

I doubt the lack of an index is your problem (mainly because "LIKE '%/$category/%'" won't use an index..).

 

Also, 18k records is -nothing-. This sounds like apache getting hung up. Is this on a shared host? Dedicated server? Virtual dedicated?

[Jeffro]

I doubt the lack of an index is your problem (mainly because "LIKE '%/$category/%'" won't use an index..).

 

Also, 18k records is -nothing-. This sounds like apache getting hung up. Is this on a shared host? Dedicated server? Virtual dedicated?

 

I wondered that..  since it was so random.  Most of the time, the query executes almost instantly (1 second).  It's shared hosting..  hostgator.