?
Solved

Web Based App Localization

Posted on 2010-08-26
8
Medium Priority
?
372 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:DalHorinek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 33548101
The table I would use:

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.
0
 
LVL 6

Author Comment

by:DalHorinek
ID: 33548585
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 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;

Open in new window

0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 33550180
>>> 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.
0
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
LVL 6

Author Comment

by:DalHorinek
ID: 33613351
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?

/* 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 |                                              |
+----+-------------+---------------------------------------------------+--------+--------------------------------------------+------------------+---------+---------------------------------------------------------+------+----------------------------------------------+

Open in new window

0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1000 total points
ID: 33615217
It looks like system_languages is at fault here.  Granted, it is only 4 rows, but with each hit it is increasing the number of rows as a multiplier.  For example, the first two tables in the "SELECT * FROM view_hotels2..." are system_languages and hotels_properties_cities.  4 x 6 = 24.  Next is hotels_properties_states, which is 1 row.  24 x 1 = 24.  Next is system_languages again with another 4 rows (24 x 4 = 96).  Next is 31 (96 x 31 = 2976), then another system_languages (2976 x 4 = 11904).  Just with the next three system_languages hits, you're already up to 761856.  It is worse on the view definition, because you have 9 references to system_languages (4 ^ 9 = 262144).

From here, I think you have two options:

1) Adjust your query to provide a static system_languages look up.  Instead of querying for all the languages, query only for the language that is needed.  You're doing that already, but you're doing it on child tables.  Either system_languages does not have a proper index, or you're not using it in your joins.  Using an index properly will change the join type for system_languages from ALL to eq_ref, which should generate a single row.

2) As you guessed, preloading your language terms in the application would remove this hit from your queries.  You could either put the terms in an include file or have a table with the terms.  Either way, you would load them into a language array at the start of the page load, based on the language selected by the user (session variable will work for storing this preference).  The execution time overhead should be pretty minimal, but you will have an increase in memory used per thread since you will need to load all language-based strings.
0
 
LVL 6

Author Comment

by:DalHorinek
ID: 33615924
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.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 33620761
>>> 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!
0
 
LVL 6

Author Comment

by:DalHorinek
ID: 33624619
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_code = view_hotels_rooms.id_language_code;

without indexes:
2277 rows in set (0.80 sec)

with indexes:
2277 rows in set (0.62 sec)

Adding where view_hotels.id_language_code = 'en'

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)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The viewer will learn how to count occurrences of each item in an array.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question