I think the question is stupid, but I did not find the answer in the materiel. For example, there is a table id (key) | name (string) | country (?) | city ​​(?)

Here is the question. I have to choose by several parameters, for example, sometimes by country and city, sometimes one thing. What is better to use in columns country and city - numerical data (indices from other tables) or string names of these countries and cities? 100% I will have 2 more tables with cities and countries:

id (key) | country (string)

id (key) | city ​​(string)

But there will definitely be no queries with JOIN, since data for cities and countries will be on the client side as well.

Those. what gets out faster

[SQL] SELECT * FROM table WHERE country = 421 AND city = 547 [/ SQL], if id (key) | name (string) | country (number) | city ​​(number) - and then the numbers must somehow index?

or

[SQL] SELECT * FROM table WHERE country = "Germany" AND city = "Berlin" [/ SQL], if id (key) | name (string) | country (string) | city ​​(string)

  • Faster will be selected by the field on which the index will be built. depends on data type weakly. but to ensure referential integrity is taken by id, since there may be errors in the names. Imagine that you made a mistake in one letter in the city and then it spread to thousands of records in the dependent table and now we need to correct the name ... and the place names usually take up more, and more volume - longer to lift from the disk - Mike

1 answer 1

Numbers will always work faster. The index on them will weigh less, will be updated and built faster. Use the btree index on the fields by which you will search (country, city).