Jeffro Posted June 16, 2011 Share Posted June 16, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/239553-is-my-problem-that-of-needing-an-index/ Share on other sites More sharing options...
requinix Posted June 16, 2011 Share Posted June 16, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/239553-is-my-problem-that-of-needing-an-index/#findComment-1230581 Share on other sites More sharing options...
awpti Posted June 16, 2011 Share Posted June 16, 2011 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? Quote Link to comment https://forums.phpfreaks.com/topic/239553-is-my-problem-that-of-needing-an-index/#findComment-1230674 Share on other sites More sharing options...
Jeffro Posted June 16, 2011 Author Share Posted June 16, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/239553-is-my-problem-that-of-needing-an-index/#findComment-1230711 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.