I recently had a host suspend some scripts of mine due to over use of mysql someone pointed out that a 'like' query uses much more resouces There was a "like" statement in the script, which i have now removed, but could that be the whole problem?
make sure you type out every field opposed to using "*" also. Try putting for indexes on the search fields also, composite index will also order the table index. Post your statement here is you like and we will see how it looks
Yes, "like" is much less efficient and will not use indexes (or will not use them efficiently). Basically, in order to handle something like %word%, the server has to read every record and check if there's a substring you are looking for. Even if the server can use an index, it would still have to go through all nodes of the index in this case. This much reading will result in unnecessary disk I/O for large databases. J.D.
Don't go overboard on the indexes though... that can be just as bad but definately avoid like if you don't absolutely need it.
ok another host has suspended my site they say that these queries are too taxing select url, date, id from news where url ='http://www.ranchero.com/xml/rss.xml?q=referers How could I make the simplier they also complained that the has used up 1.2 gigs of space in mysql
The joys of hosting but that would be a "full table scan" and that is taxing. If you are going to have a 1.2gig database then you need to get smarter about your table design. I'd maybe have a column for source, indexed, that you can search on and another smaller table with the sources.