Web Based App Localization
Hi everybody,
I'm about to re-implement localizations.
I have a table with some properties (for example fruits categories, countries, cities etc) and for each type of property there is a localization table.
I need to select localizations for given language and if not defined, then select default language.
I use for this two views in database. It cross joins with languages and assigns labels and the second one joins first view on self to use default language label where missing.
And these views are also joined to other tables where I need those data.
And it's of course slow.
So I've been searching for other ways how to solve it.
I got idea about selecting all properties into application and then select data without these localizations and after that merge it.
(Like preload localization and then use it)
What's best way how to do localizations?
I don't want to do localizations in languge per column style, cuz if you want to add new language, you have to alter table and that's not good approach I guess.
I'm about to re-implement localizations.
I have a table with some properties (for example fruits categories, countries, cities etc) and for each type of property there is a localization table.
I need to select localizations for given language and if not defined, then select default language.
I use for this two views in database. It cross joins with languages and assigns labels and the second one joins first view on self to use default language label where missing.
And these views are also joined to other tables where I need those data.
And it's of course slow.
So I've been searching for other ways how to solve it.
I got idea about selecting all properties into application and then select data without these localizations and after that merge it.
(Like preload localization and then use it)
What's best way how to do localizations?
I don't want to do localizations in languge per column style, cuz if you want to add new language, you have to alter table and that's not good approach I guess.
ASKER
That's almost exactly how I have my tables.
I attach a code which demonstrates my tables + two views for it.
But when I join last localized view for other tables to select localized complete data, it's slow, cuz there are too many system_languages joins etc.
It would be nice to optimize it somehow.
I attach a code which demonstrates my tables + two views for it.
But when I join last localized view for other tables to select localized complete data, it's slow, cuz there are too many system_languages joins etc.
It would be nice to optimize it somehow.
I have these tables:
describe properties_cities;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| id_state | int(11) | NO | MUL | NULL | |
| city | varchar(255) | NO | | NULL | |
| position | int(11) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
/* this one adds localizations for properties_cities */
describe properties_cities_locales
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| id_city | int(11) | NO | MUL | NULL | |
| id_language_code | varchar(255) | NO | MUL | NULL | |
| l10n_city | varchar(255) | NO | | NULL | |
+------------------+--------------+------+-----+---------+----------------+
/* And also there is a available languages table */
describe system_languages;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| code | varchar(2) | NO | | NULL | |
| language | varchar(100) | NO | | NULL | |
| default | tinyint(1) | YES | | 0 | |
+----------+--------------+------+-----+---------+----------------+
/* view for selecting property + language localization for each language */
DROP VIEW IF EXISTS view_property_cities;
CREATE VIEW view_property_cities (id, city, id_state, position, id_language_code, l10n_city) AS
SELECT properties_cities.id,
properties_cities.city,
properties_cities.id_state,
properties_cities.position,
system_languages.code AS id_language_code,
properties_cities_locales.l10n_city
FROM properties_cities
CROSS JOIN system_languages
LEFT JOIN properties_cities_locales
ON properties_cities.id = properties_cities_locales.id_city
AND properties_cities_locales.id_language_code = system_languages.code
/* also I need to use default language localization when there is no localization for selected language, it's based on previous view and do self join */
DROP VIEW IF EXISTS view_property_cities_localized;
CREATE VIEW view_property_cities_localized (id, city, id_state, position, id_language_code, l10n_city) AS
SELECT view_property_cities.id,
view_property_cities.city,
view_property_cities.id_state,
view_property_cities.position,
view_property_cities.id_language_code,
IFNULL(view_property_cities.l10n_city,scnd.l10n_city) AS l10n_city
FROM view_property_cities
LEFT JOIN system_languages
ON system_languages.default = 1
LEFT JOIN view_property_cities AS scnd
ON view_property_cities.id = scnd.id AND scnd.id_language_code = system_languages.code;
>>> when I join last localized view for other tables to select localized complete data, it's slow
That is most likely just needing some optimization. If you post some of the slower queries along with their EXPLAIN results (and probably the structure of the source tables), we can work from that angle.
That is most likely just needing some optimization. If you post some of the slower queries along with their EXPLAIN results (and probably the structure of the source tables), we can work from that angle.
ASKER
I'm sorry for the lag, I had a vacation and there was no internet connection.
Back to my question, I have a table of hotels with some properties and data.
And there are tables as cities and their localizations ... see code below.
If I want to join hotels table to another (for example rooms), which uses same views, it's slow, but not that much. Anyway I think that's inefficient to join same (locales) tables again and again.
Wouldn't it be better to pre-load locales to application?
Back to my question, I have a table of hotels with some properties and data.
And there are tables as cities and their localizations ... see code below.
If I want to join hotels table to another (for example rooms), which uses same views, it's slow, but not that much. Anyway I think that's inefficient to join same (locales) tables again and again.
Wouldn't it be better to pre-load locales to application?
/* hotels view using other view_properties */
CREATE VIEW view_hotels2 (
id, name,
id_city, city, l10n_city,
id_state, state, l10n_state,
id_location, location, l10n_location,
id_city_area, city_area, l10n_city_area,
address, phone, email,
number_of_rooms, check_in, check_out,
id_accommodation_type, accommodation_type, l10n_accommodation_type,
id_accommodation_standard, accommodation_standard, l10n_accommodation_standard,
gmaps_coordinates,
youtube_video,
publish,
position,
commission,
id_language_code) AS
SELECT hotels.id,
hotels.name,
view_property_cities.id, view_property_cities.city, view_property_cities.l10n_city,
view_property_states.id, view_property_states.state, view_property_states.l10n_state,
view_property_locations.id, view_property_locations.location, view_property_locations.l10n_location,
view_property_city_areas.id, view_property_city_areas.city_area, view_property_city_areas.l10n_city_area,
hotels.address,
hotels.phone,
hotels.email,
hotels.number_of_rooms,
hotels.check_in,
hotels.check_out,
view_property_accommodation_types.id, view_property_accommodation_types.accommodation_type, view_property_accommodation_types.l10n_accommodation_type,
view_property_accommodation_standards.id, view_property_accommodation_standards.accommodation_standard, view_property_accommodation_standards.l10n_accommodation_standard,
hotels.gmaps_coordinates,
hotels.youtube_video,
hotels.publish,
hotels.position,
hotels.commission,
view_property_cities.id_language_code
FROM hotels
LEFT JOIN view_property_cities
ON view_property_cities.id = hotels.id_city
LEFT JOIN view_property_states
ON view_property_states.id = view_property_cities.id_state
AND view_property_cities.id_language_code = view_property_states.id_language_code
LEFT JOIN view_property_locations
ON view_property_locations.id = hotels.id_location
AND view_property_locations.id_language_code = view_property_cities.id_language_code
LEFT JOIN view_property_city_areas
ON view_property_city_areas.id = view_property_locations.id_city_area
AND view_property_city_areas.id_language_code = view_property_locations.id_language_code
LEFT JOIN view_property_accommodation_types
ON view_property_accommodation_types.id = hotels.id_accommodation_type
AND view_property_accommodation_types.id_language_code = view_property_cities.id_language_code
LEFT JOIN view_property_accommodation_standards
ON view_property_accommodation_standards.id = hotels.id_accommodation_standard
AND view_property_accommodation_standards.id_language_code = view_property_cities.id_language_code;
SELECT * FROM view_hotels2 WHERE id_language_code = 'en'
....
186 rows in set (0.03 sec)
explain select * from view_hotels2 where id_language_code = 'en';
+----+-------------+---------------------------------------------------+--------+--------------------------------------------+------------------+---------+---------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------------------------------+--------+--------------------------------------------+------------------+---------+---------------------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | hotels_properties_cities | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using join buffer |
| 1 | SIMPLE | hotels_properties_states | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels_properties_cities.id_state | 1 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_states_locales | ref | id_state,id_language_code | id_language_code | 8 | travelcook_dev.system_languages.code | 1 | |
| 1 | SIMPLE | hotels | ref | id_city | id_city | 4 | travelcook_dev.hotels_properties_cities.id | 31 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_locations | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels.id_location | 1 | |
| 1 | SIMPLE | hotels_properties_locations_locales | ref | id_location,id_language_code | id_location | 4 | travelcook_dev.hotels_properties_locations.id | 4 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_city_areas | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels_properties_locations.id_city_area | 1 | |
| 1 | SIMPLE | hotels_properties_city_areas_locales | ref | id_city,id_language_code | id_city | 4 | travelcook_dev.hotels_properties_city_areas.id | 4 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_accommodation_types | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels.id_accommodation_type | 1 | |
| 1 | SIMPLE | hotels_properties_accommodation_types_locales | ref | id_accommodation_type,id_language_code | id_language_code | 8 | travelcook_dev.system_languages.code | 2 | |
| 1 | SIMPLE | hotels_properties_accommodation_standards | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels.id_accommodation_standard | 1 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_accommodation_standards_locales | ref | id_accommodation_standard,id_language_code | id_language_code | 8 | travelcook_dev.system_languages.code | 3 | |
| 1 | SIMPLE | hotels_properties_cities_locales | ref | id_city,id_language_code | id_city | 4 | travelcook_dev.hotels_properties_cities.id | 4 | |
+----+-------------+---------------------------------------------------+--------+--------------------------------------------+------------------+---------+---------------------------------------------------------+------+----------------------------------------------+
mysql> explain select * from view_hotels2 left join view_hotels_rooms on view_hotels2.id = view_hotels_rooms.id_hotel and view_hotels2.id_language_code = view_hotels_rooms.id_language_code where view_hotels2.id_language_code = 'en';
+----+-------------+---------------------------------------------------+--------+--------------------------------------------+------------------+---------+---------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------------------------------+--------+--------------------------------------------+------------------+---------+---------------------------------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | hotels_properties_cities | ALL | PRIMARY | NULL | NULL | NULL | 6 | Using join buffer |
| 1 | SIMPLE | hotels_properties_states | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels_properties_cities.id_state | 1 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_states_locales | ref | id_state,id_language_code | id_language_code | 8 | travelcook_dev.system_languages.code | 1 | |
| 1 | SIMPLE | hotels | ref | id_city | id_city | 4 | travelcook_dev.hotels_properties_cities.id | 31 | |
| 1 | SIMPLE | hotels_properties_locations | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels.id_location | 1 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_locations_locales | ref | id_location,id_language_code | id_location | 4 | travelcook_dev.hotels.id_location | 4 | |
| 1 | SIMPLE | hotels_properties_city_areas | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels_properties_locations.id_city_area | 1 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_city_areas_locales | ref | id_city,id_language_code | id_city | 4 | travelcook_dev.hotels_properties_city_areas.id | 4 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_rooms | ref | id_hotel,id_room_type | id_hotel | 4 | travelcook_dev.hotels.id | 3 | |
| 1 | SIMPLE | hotels_properties_room_types | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels_rooms.id_room_type | 1 | |
| 1 | SIMPLE | hotels_properties_room_types_locales | ref | id_room_type,id_language_code | id_room_type | 4 | travelcook_dev.hotels_rooms.id_room_type | 4 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_room_types | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels_properties_room_types.id | 1 | Using index |
| 1 | SIMPLE | hotels_properties_room_types_locales | ref | id_room_type,id_language_code | id_room_type | 4 | travelcook_dev.hotels_properties_room_types.id | 4 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_accommodation_types | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels.id_accommodation_type | 1 | |
| 1 | SIMPLE | hotels_properties_accommodation_types_locales | ref | id_accommodation_type,id_language_code | id_language_code | 8 | travelcook_dev.system_languages.code | 2 | |
| 1 | SIMPLE | hotels_properties_accommodation_standards | eq_ref | PRIMARY | PRIMARY | 4 | travelcook_dev.hotels.id_accommodation_standard | 1 | |
| 1 | SIMPLE | system_languages | ALL | NULL | NULL | NULL | NULL | 4 | |
| 1 | SIMPLE | hotels_properties_accommodation_standards_locales | ref | id_accommodation_standard,id_language_code | id_language_code | 8 | travelcook_dev.system_languages.code | 3 | |
| 1 | SIMPLE | hotels_properties_cities_locales | ref | id_city,id_language_code | id_city | 4 | travelcook_dev.hotels_properties_cities.id | 4 | |
+----+-------------+---------------------------------------------------+--------+--------------------------------------------+------------------+---------+---------------------------------------------------------+------+----------------------------------------------+ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't know why I didn't have an index on system_languages, anyway now it's ok. Now it uses references to the table and it's much better. I'll see how quick ii will be when all parts are used.
Thank you very much for your help and patience.
Thank you very much for your help and patience.
>>> I don't know why I didn't have an index on system_languages,
One of those things that fell through the cracks, no doubt. It happens. I would be interested in seeing the statistics of with and without, if you don't mind posting them.
Good luck!
One of those things that fell through the cracks, no doubt. It happens. I would be interested in seeing the statistics of with and without, if you don't mind posting them.
Good luck!
ASKER
Sure, it's not a problem, here they are:
without indexes:
select * from view_hotels;
761 rows in set (0.22 sec)
select * from view_hotels where id_language_code = 'en';
190 rows in set (0.05 sec)
with indexes:
select * from view_hotels
761 rows in set (0.01 sec)
select * from view_hotels where id_language_code = 'en';
190 rows in set (0.04 sec)
When using condition id_language_code = 'en', there is not such difference, but
this query:
select * from
view_hotels
left join view_hotels_rooms
on view_hotels.id = view_hotels_rooms.id_hotel
and view_hotels.id_language_co
without indexes:
2277 rows in set (0.80 sec)
with indexes:
2277 rows in set (0.62 sec)
Adding where view_hotels.id_language_co
without indexes:
569 rows in set (0.25 sec)
with indexes:
569 rows in set (0.14 sec)
548 rows in set (0.16 sec)
without indexes:
select * from view_hotels;
761 rows in set (0.22 sec)
select * from view_hotels where id_language_code = 'en';
190 rows in set (0.05 sec)
with indexes:
select * from view_hotels
761 rows in set (0.01 sec)
select * from view_hotels where id_language_code = 'en';
190 rows in set (0.04 sec)
When using condition id_language_code = 'en', there is not such difference, but
this query:
select * from
view_hotels
left join view_hotels_rooms
on view_hotels.id = view_hotels_rooms.id_hotel
and view_hotels.id_language_co
without indexes:
2277 rows in set (0.80 sec)
with indexes:
2277 rows in set (0.62 sec)
Adding where view_hotels.id_language_co
without indexes:
569 rows in set (0.25 sec)
with indexes:
569 rows in set (0.14 sec)
548 rows in set (0.16 sec)
id (autoincrement, PK)
PropertyID (FK to properties table)
LanguageID (EN/DE/SP/etc)
Label (varchar)
As long as you do not need more than one label per row, it is not very complicated at all.