Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Web Based App Localization

Posted on 2010-08-26
8
Medium Priority
?
373 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

618 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