SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LEFT JOIN games g ON s.in_game LIKE CONCAT( g.gid,'|%' ) - Query efficient?

    Hey guys,

    just wondering whether this query can be classed as an efficient query with the correct indexes? (the concat() may be the problem)

    Code:
    SELECT * FROM ibf_sessions s LEFT JOIN ibf_games_list g ON s.in_game LIKE CONCAT( g.gid, '|%' ) WHERE s.running_time > 1109979202 AND s.location='Arcade,,' ORDER BY s.running_time DESC
    michael.Crabbe

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it might be classed as efficient, but i'm betting it won't actually be efficient

    i'm guessing you have more than one value in g.gid?

    also, is s.in_game a numeric column? the way the query is written, the join will work nicely whenever g.gid contains s.in_game as the leftmost characters, and s.in_game is itself a character column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    g.gid = integer
    s.in_game = varchar (typically in the format '1|play')
    michael.Crabbe

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, that could still give you problems

    let's say g.id is 1 and s.in_game is '1|play'

    your query translates to this --

    ... WHERE '1|play' LIKE CONCAT( 1, '|%' )

    this will work, but i'm not sure if it will be efficient

    do an EXPLAIN on the query to find out more
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't really know why you've repeated my query to me - I already know that it works - I wrote it myself. You have said it could cause problems, but how could it? Why haven't you said?

    I was just wondering whether I should just do a second query rather than use the JOIN, which is quicker, or whatnot.
    michael.Crabbe

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please do forgive me for not precisely answering your question immediately!!

    the problems could be --

    (1) the design of the data means that you have to use LIKE, and this might not perform well

    (2) the possibility exists (in my mind, at least, although it won't be the first time that i've encountered an example like this) that there might be more than one gid value in the in_game column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you mean by "more than one gid value in the in_game column"?

    Could you show me an example please?
    michael.Crabbe

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, i'd rather not, you're too crabby!

    if you have only the one gid in s.in_game, and it's left-aligned, then it's not as fast as an integer join, but it's not as bad as if there were a string of numbers in it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, lol.

    I meant like an example of the following:
    • only the one gid in s.in_game, and it's left-aligned
    • a string of numbers in it
    • Thanks (can't get out of the list :|)
    michael.Crabbe

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, an example of "only the one gid in s.in_game, and it's left-aligned" -- '1|play'

    an example of "a string of numbers in it" --

    by analogy, some people implement an "articles" table, and within it, they define a "categories" column, and inside this column, they store a comma-delimited list of category ids, to relate the article to the various categories it belongs to

    e.g. '2,4,9,37'

    this is very poor design, despite the fact that php has neat-o functions like "blow-up" to convert a list of numbers to an array, because when you want to do a join in sql with this varchar, it becomes difficult if not impossible to get it to perform efficiently (it usually requires a table scan)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I'll definitely only be using the 1|play format.
    michael.Crabbe

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if you don't mind my asking, what other values for gid=1 would you have in s.in_game? and could you show the CREATE TABLE for the ibf_sessions table?

    because i'm thinking if you split it into two columns, the join will be a lot faster (assuming you also declare a compound index)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict CrabbyX's Avatar
    Join Date
    Aug 2004
    Location
    England, UK
    Posts
    249
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah it would be better I know that for sure, but its not my table design, and quite a bit of scattered code would require adjusting.
    michael.Crabbe


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •