Witam,

Jak trzymać miasta w bazie. Otóż mam tabele 'users', każdy użytkownik ma swoje miasto. Pytanie czy poniższe rozwiązanie jest optymalne.

Chce zrobić tak:
Utworzyć tabelę 'cities' z polami:
id | city

id - zawiera nazwe miasta bez polskich znaków i będzie kluczem dla każdego miasta
city - zawiera poprawną nazwę miasta z polskimi znakami


Tabela 'users' w polu 'cities_id' będzie zawierać włąśnie klucz obcy miasta (czyli wartość z pola 'id' z tabeli 'cities')
Dlaczego jako klucza nie chce używać liczby INT, ponieważ dzięki klucza w formie jak wyżej można będzie sortować użytkowników po mieście.
W przypadku klucza typu INT sortowanie odpada. Gdyż w polu 'cities_id' w tabeli 'users' nie ma możliwości sortowania w ten sposób po mieście.

tabela users
id_user, name, id_city

tabela cities
id, city

[SQL] pobierz, plaintext 
SELECT u.name, c.city FROM users u, cities c WHERE c.id = u.id_city ORDER BY c.city ASC 
[SQL] pobierz, plaintext 

Zrób to na kluczach INT.

Tak tylko że jak w bazie users będę miał np milion użytkowników, to baza zdechnie delikatnie mówiąc.

Rozważam takie zapytanie na kluczu jako INT

[SQL] pobierz, plaintext 
SELECT u.name, c.city FROM cities c LEFT JOIN users u ON c.id = u.id_city,  WHERE c.id = u.id_city ORDER BY c.city ASC 
[SQL] pobierz, plaintext 

I index na pole city w tabeli 'cities'

Chociaż jeśli dobrze myślę że index nie zadziała tak jak powinień, zadziała dla tabeli cities ale użytkownicy i tak będą sortowane beż użycia jakiegoś indexu tzn wolno (mówiąc o milion uzytkownikach)

W Twoim zapytaniu powinieneś mieć utworzone 2 indeksy:
1. na kluczu obcym w tabeli użytkowników łączących rekord z tabelą miast.
2. na nazwie miasta w tabeli miast.

Jeśli dodatkowo chciałbyś sortować użytkowników również po nazwie użytkownika to załóż indeks na to pole (najlepiej unikalny).

PS: Ten dodatkowy WHERE jest kompletnie zbędny, a dodatkowo połączenie LEFT JOIN + ten WHERE da tak naprawdę samo JOIN (czyli jeśli użytkownik nie ma przypisanego miasta to nie zostanie wyświetlony).

Racja ten WHERE nie jest tam potrzebny, Mały błąd

Właśnie przeprowadziłem testy;
Do bazy users dodałem około 3 mln użytkowników, każdy ma jakieś miasto
Miast jest ponad 100.

Teraz zapytanie SQL:

[SQL] pobierz, plaintext 
SELECT `u`.`name`, `u`.`surname`, `c`.`city` FROM `users` AS `u` INNER JOIN `cities` AS `c` ON u.id_city=c.id_city ORDER BY `c`.`city` DESC LIMIT 20
[SQL] pobierz, plaintext 

Dodałem idexy na pola:
id_city w tabeli users
city w tabeli cities

Zapytanie wykonuje się powyżej 30 sekund, czyli powyżej normy :/

Może jakieś pomysły ?

Możesz spróbować stworzyć widok, który będzie połączeniem tych dwóch tabel. Spróbuj - powinno być szybciej ze względu na wbudowany system cachowania.

Pzdr.

Zauważyłem że zmiana z INNER JOIN na RIGHT JOIN spowodowało że czas spadłz okolo 35 s. do 0.04 s

[SQL] pobierz, plaintext 
SELECT `u`.`name`, `u`.`surname`, `c`.`city` FROM `users` AS `u` RIGHT JOIN `cities` AS `c` ON u.id_city=c.id_city ORDER BY `c`.`city` DESC LIMIT 20
[SQL] pobierz, plaintext 

Z tego wynika że pole po którym następuje sortowanie musi pochodzić z 1 tabeli wiec w tym wypadku jest nią tabela 'cities'.
Wydaje mi się że o to chodziło