SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    using int vs using varchar with join

    I have tables cities and users. Does it make sense to use for a search int together with join instead of varchar? Does it make any huge difference?

    Example:
    Code:
    SELECT userId FROM users WHERE cityName LIKE '%searchterm%'
    vs

    Code:
    SELECT userId FROM users
    INNER JOIN on cities users.cityId=cities.cityId
    WHERE cities.cityName LIKE '%searchterm%'
    Which one is better or is it about the same?

    Tnx!

  2. #2
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would like to modify my question:

    I am wondering in which examples joins are still making sense and not just unecessary complicating things. I know it depends on many factors, so I will give 2 real examples:

    1.
    I have tables cities and users. Does it make sense to use for a search int together with join instead of varchar? Does it make any huge difference?

    Code:
    SELECT userId FROM users WHERE cityName LIKE '%searchterm%'
    vs

    PHP Code:
    SELECT userId FROM users
    INNER JOIN on cities users
    .cityId=cities.cityId
    WHERE cities
    .cityName LIKE '%searchterm%' 
    2.
    In both cases I have tables users and memberships. The difference is that in first case, I there is extra field in user table, a field membership. "Golden" users are displayed in almost every page, so this query is very often.

    PHP Code:
    SELECT userId FROM users ORDER BY membership 
    vs
    PHP Code:
    SELECT userId FROM users
    INNER JOIN memberships ON users
    .userId=memberships.userId
    ORDER BY memberships
    .membership 
    Tnx!

  3. #3
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,870
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Only one of the options will be available if the tables are defined correctly since you will not have both fields in the user table. The user table should have as a foreign key whatever the primary key is on the other table.

    With correctly defined tables only one variant of the calls will be possible.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you for your answer. I am just not sure about first example.

    The thing is that I need cities in both tables in any way (in user table I store user city and in the cities table a list of all cities). The difference is just having city name in users table as varchar or having in users table city id (and not city name) as int and use join to get city name from cities table.
    users
    userId | city | ...
    cities
    cityId | city | cityCountry
    If I use as varchar, it will need to go through all varchar rows in users table. If I use 2 tables, it will need to go through 10.000 int rows instead of 10.000 varchar rows and only through about 15 int rows from cities table.

    And since int is faster than varchar, I thought it will be better to use join. Is that correct?

  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)
    Quote Originally Posted by meee View Post
    And since int is faster than varchar, I thought it will be better to use join. Is that correct?
    no, it isn't, not always

    (be very careful about general statements like "int is faster than varchar" because general statements are by definition out of context, and the only thing that matters to you is your context)

    why not do both? use a VARCHAR city name in the users table, and a VARCHAR city name in the cities table

    i guess the next question i would ask is why do you need a cities table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2009
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks again! I need this table to show all cities in registration form and in search and browse user pages where users can browse and search members by city. My main wondering was just if it is better to use in search for such situation int or varchar.

  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)
    okay, in order to show a list of cities in a form, you do need a cities table

    now, what do you think of my idea of using the varchar city name in the users table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •