SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to avoid LIKE in this query

    I'm trying to get all the items from my database where the artist's first letter is between a given range(for example A-F).

    In my previous system I did this by using the query:
    Code:
    SELECT * FROM distro_items 
            WHERE LCASE(artist) RLIKE '^".strtolower($like)."' 
            ORDER BY artist ASC
    
    (PHP code for $like:)
    $like = "[" . $_GET['f'] . "-" . $_GET['t'] . "]";
    I now realise that you should try to avoid LIKE in queries as there will definitely be a scan, is there an alternative I can use here? My table structure is no longer like the one above either.. the item is spread across 4 tables but I have a query to join them.

    Code:
    SELECT i.itemid, a.artist, i.album, i.format FROM distro_items AS i
    	INNER JOIN distro_artist_album AS aa ON aa.itemid = i.itemid
    	INNER JOIN distro_artist AS a ON a.artistid = aa.artistid
    	ORDER BY i.itemid DESC
    So I'm trying to have this query but only selecting where a.artist begins between the range $from - $to. Can anyone help?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Teeej
    I now realise that you should try to avoid LIKE in queries as there will definitely be a scan,
    that's only true if the pattern starts with a wildcard

    so this would cause a scan --

    ... where foo like '%bar%'

    and this would utilize an index (if one exists) --

    ... where foo like 'bar%'

    for checking the first letter of a name, you can use this --

    ... where left(name,1) between 'a' and 'f'

    and it will still use the index

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I have another problem related to the same query.

    Code:
    SELECT i.itemid, a.artist, i.album, i.format FROM distro_items AS i
    	INNER JOIN distro_artist_album AS aa ON aa.itemid = i.itemid
    	INNER JOIN distro_artist AS a ON a.artistid = aa.artistid
            WHERE left(a.artist,1) between '$f' and '$t'
    	ORDER BY a.artist
    Successfully returns all the items where one of the artists is between $f and $t. However say an item has the following information

    Artists: Funeral Diner / Welcome The Plague Year
    Album: Split 7"

    This is the output for the following ranges:

    RANGE = A-F

    Output should be:
    Funeral Diner - Welcome The Plague Year
    Split 7"

    What it is:

    Funeral Diner
    Split 7"

    Problem: The other artist of this record isn't in the artist range so it isn't displayed.
    RANGE = F-W

    Funeral Diner
    Split 7"

    Welcome The Plague Year
    Split 7"

    Problem: My code to compensate for not being able to use GROUP_CONCAT() checks for changes in the itemid as it scans results. Now that I want the results to be output in alphabetical order, I need to order by artist. This ruins my code.
    To solve the first problem, I think I'm going to have to peform another join on my code to get all the artists involved... can I do this in one query or does it involve another extra query per item?

    I think the second is something I'm going to have to solve in the PHP side of things, but if you could see a way to solve it with MySQL then please share!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sounds like you have multiple artists per item

    if you want all the artists per item, then you do need another join -- start the search with one occurrence of the artist table (find stuff by artist 1, constrained by first letter if desired) and then join again to the artist table to get all the other artists for each item (note you'll also get artist 1 again)

    it's messier but very straightforward
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, I do have multiple artists per item.. thought I mentioned that.

    Is that two queries then? I'm confused as to how to structure what you're saying.. but that was how I thought I'd have to do it.

    Edit; Here are my tables:

    Code:
    distro_item
    itemid, album, etc.
    
    distro_artist_album
    itemid, artistid
    
    distro_artist
    artistid
    So I'd search each row of artist_album and check for each itemid if the artist assigned to artistid began with a or f. If any of them did, I'd then get the artists and item iformation for that item.

    Putting that into MySQL is where I'm confused.. I think I'm doing the first part with the original query I just don't know whether to operate on the results of that query with PHP or modify the query itself to do the second part. I haven't ever worked with SQL on one(or many)-to-many relationships.. bit off more than I could chew I think.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select A1.name
         , I.album
         , A2.name
      from distro_artist as A1
    inner
      join distro_artist_album as AA1
        on A1.artistid = AA1.artistid
    inner
      join distro_item as I
        on AA1.itemID = I.itemid
    left outer
      join distro_artist_album as AA2   
        on AA1.itemID = AA2.itemid
       and AA1.artistid <> AA2.artistid
    left outer
      join distro_artist as A2
        on AA2.artistid = A2.artistid      
     where left(A1.name,1) between 'a' and 'f'
    order
        by A1.name
         , I.album
         , A2.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, thank you so much! That works, although I'm not sure I understand it, hehe.

    You have been extremely helpful in all the questions I've asked so thanks again!

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops, I jumped the gun.. it doesn't work actually.

    Test Data in output form:
    Code:
    Funeral Diner / Raein - Split - 7
    Funeral Diner / Welcome The Plague Year - Split - 7"
    City of Caterpillar - Self-Titled - LP
    Funeral Diner - The Wicked EP - 10"
    Funeral Diner - The Wicked EP - CD	
    Jerome's Dream / Orchid - Skull Split - 10"
    Result set from that query (with some changes - changed the order by to itemid for clarity and had to add itemid to what I'm selecting)

    Code:
     itemid   	  artist   	  album   	  artist
    2 	Funeral Diner 	The Wicked EP 	Funeral&nbsp;Diner
    3 	Funeral Diner 	The Wicked EP 	Funeral&nbsp;Diner
    4 	City of Caterpillar 	Self-Titled 	City&nbsp;of&nbsp;Caterpillar
    5 	Welcome The Plague Year 	Split 	Welcome&nbsp;The&nbsp;Plague&nbsp;Year
    6 	Raein 	Split 	Raein
    I don't know why those &nbsp; are showing up on the A2.artist column, but anyway.. when there is two artists it's the artist that were previously not showing up that are now showing up in both columns. I expect that what I should have been getting was:

    Code:
     itemid   	  artist   	  album   	  artist
    2 	Funeral Diner 	The Wicked EP 	Funeral&nbsp;Diner
    3 	Funeral Diner 	The Wicked EP 	Funeral&nbsp;Diner
    4 	City of Caterpillar 	Self-Titled 	City&nbsp;of&nbsp;Caterpillar
    5 	Funeral Diner	Split 	Welcome&nbsp;The&nbsp;Plague&nbsp;Year
    6 	Funeral Diner	Split 	Raein
    And then just process that.

    The query that got the first output was:

    Code:
    select I.itemid
         , A1.artist
         , I.album
         , A2.artist
       from distro_artist as A1
    inner
       join distro_artist_album as AA1
         on A1.artistid = AA1.artistid
    inner
       join distro_items as I
         on AA1.itemID = I.itemid
    left outer
       join distro_artist_album as AA2
         on AA1.itemID = AA2.itemid
        and AA1.artistid <> AA2.artistid
    left outer
       join distro_artist as A2
         on AA2.artistid = A2.artistid
      where left(A1.artist,1) between 'a' and 'z'
     order
        by I.itemid
            , A1.artist
    	, I.album
    	, A2.artist

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2005
    Location
    Glasgow, Scotland
    Posts
    97
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This query worked

    Code:
    SELECT i.itemid
    	, a.artist as test
    	, i.album
    	, i.format 
    	, a2.artist
        FROM distro_items AS i
    INNER 
        JOIN distro_artist_album AS aa 
          ON aa.itemid = i.itemid
    INNER 
        JOIN distro_artist AS a 
          ON a.artistid = aa.artistid
    LEFT OUTER
        JOIN distro_artist_album as aa2    
          ON i.itemid = aa2.itemid
         AND aa.artistid <> aa2.artistid
    LEFT OUTER
        JOIN distro_artist as a2
          ON aa2.artistid = a2.artistid
       WHERE left(a.artist,1) between 'a' and 'f'
      ORDER BY test, i.itemid
    Code:
     itemid   	  test   	  album   	  format   	  artist
    2 	Funeral Diner 	The Wicked EP 	CD 	NULL
    3 	Funeral Diner 	The Wicked EP 	10" 	NULL
    4 	City of Caterpillar 	Self-Titled 	LP 	NULL
    5 	Funeral Diner 	Split 	7" 	Welcome The Plague Year
    6 	Funeral Diner 	Split 	7 	Raein
    Edit - Actually, the other query works too, just need to distinguish between the artists with an alias. At least I understand joins now! Thanks a lot for the help r937.


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
  •