1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

'like' verus '=' in terms of resources

Discussion in 'MySQL' started by ferret77, Sep 15, 2005.

  1. #1
    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?
     
    ferret77, Sep 15, 2005 IP
  2. ServerUnion

    ServerUnion Peon

    Messages:
    3,611
    Likes Received:
    296
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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
     
    ServerUnion, Sep 15, 2005 IP
  3. iTISTIC

    iTISTIC Peon

    Messages:
    140
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #3
    If you post some of your code maybe we can help?
     
    iTISTIC, Sep 15, 2005 IP
  4. J.D.

    J.D. Peon

    Messages:
    1,198
    Likes Received:
    65
    Best Answers:
    0
    Trophy Points:
    0
    #4
    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.
     
    J.D., Sep 15, 2005 IP
  5. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #5
    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.
     
    sarahk, Sep 15, 2005 IP
  6. ferret77

    ferret77 Heretic

    Messages:
    5,276
    Likes Received:
    230
    Best Answers:
    0
    Trophy Points:
    0
    #6
    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
     
    ferret77, Oct 17, 2005 IP
  7. sarahk

    sarahk iTamer Staff

    Messages:
    28,494
    Likes Received:
    4,457
    Best Answers:
    123
    Trophy Points:
    665
    #7
    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.
     
    sarahk, Oct 17, 2005 IP