Solved

Web Based App Localization

Posted on 2010-08-26
8
365 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
  • 4
  • 4
8 Comments
 
LVL 50

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 50

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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 50

Accepted Solution

by:
Steve Bink earned 250 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 50

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now