Using int vs using varchar with join

Databases
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?

Example:

SELECT userId FROM users WHERE cityName LIKE '%searchterm%'

vs

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

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?

3

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.

4

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

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?

6

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?

SELECT userId FROM users WHERE cityName LIKE '%searchterm%'

vs

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.

SELECT userId FROM users ORDER BY membership

vs

SELECT userId FROM users
INNER JOIN memberships ON users.userId=memberships.userId
ORDER BY memberships.membership

Tnx!

7

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.