Query on large i18n table slow in CakePHP

Question

The query below, using CakePHP's i18n table, takes around 1000ms to run. My i18n table is large - it has around 600,000 rows. I have the default indexes on this table, as per the Cake schema.

How can I speed queries like this up? I am caching them where possible but when the cache needs to be cleared these queries are run, and they slow down the performance quite noticeably.

SELECT `Category`.`id`, `Category`.`slug`, `Category`.`name`, `Category`.`description`, `Category`.`head_title`, `Category`.`display_title`, `Category`.`category_count`, `Category`.`product_count`, `Category`.`parent_id`, `Category`.`lft`, `Category`.`rght`, `Category`.`sort_order`, `Category`.`created`, `Category`.`modified`, `Category`.`image_processed`, ((rght - lft) = 1) AS `Category__is_child`, (`I18n__name`.`content`) AS `Category__i18n_name`, (`I18n__description`.`content`) AS `Category__i18n_description`, (`I18n__slug`.`content`) AS `Category__i18n_slug`, (`I18n__head_title`.`content`) AS `Category__i18n_head_title`, (`I18n__meta_description`.`content`) AS `Category__i18n_meta_description`, (`I18n__heading_title`.`content`) AS `Category__i18n_heading_title` FROM `fracmerl_dev`.`categories` AS `Category`
    INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__name` 
        ON (`Category`.`id` = `I18n__name`.`foreign_key`
        AND `I18n__name`.`model` = 'Category' 
        AND `I18n__name`.`field` = 'name'
        AND `I18n__name`.`locale` = 'eng')
    INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__description`
        ON (`Category`.`id` = `I18n__description`.`foreign_key`
        AND `I18n__description`.`model` = 'Category'
        AND `I18n__description`.`field` = 'description' 
        AND `I18n__description`.`locale` = 'eng')
    INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__slug`
        ON (`Category`.`id` = `I18n__slug`.`foreign_key`
        AND `I18n__slug`.`model` = 'Category'
        AND `I18n__slug`.`field` = 'slug'
        AND `I18n__slug`.`locale` = 'eng')
    INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__head_title`
        ON (`Category`.`id` = `I18n__head_title`.`foreign_key`
        AND `I18n__head_title`.`model` = 'Category'
        AND `I18n__head_title`.`field` = 'head_title'
        AND `I18n__head_title`.`locale` = 'eng')
    INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__meta_description`
        ON (`Category`.`id` = `I18n__meta_description`.`foreign_key`
        AND `I18n__meta_description`.`model` = 'Category'
        AND `I18n__meta_description`.`field` = 'meta_description'
        AND `I18n__meta_description`.`locale` = 'eng')
    INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__heading_title`
        ON (`Category`.`id` = `I18n__heading_title`.`foreign_key`
        AND `I18n__heading_title`.`model` = 'Category'
        AND `I18n__heading_title`.`field` = 'heading_title'
        AND `I18n__heading_title`.`locale` = 'eng')
WHERE `I18n__slug`.`content` = 'category-slug'
ORDER BY `Category`.`sort_order` ASC, `Category`.`name` ASC
LIMIT 1

Edit - the indexes, the result of SHOW INDEX FROM i18n

i18n    0   PRIMARY 1   id  A   598455  NULL    NULL        BTREE
i18n    1   locale  1   locale  A   5   NULL    NULL        BTREE
i18n    1   model   1   model   A   3   NULL    NULL        BTREE
i18n    1   row_id  1   foreign_key A   18701   NULL  NULL  BTREE
i18n    1   field   1   field   A   9   NULL    NULL        BTREE

Thanks Dave, have added these to the question. – Will Jul 17 '13 at 13:38 — Will, Jul 17 '13 at 13:38

score 1 · Answer 1 · answered Jul 18 '13 at 07:16

1

If your i18n table is large try to split it up and use Multiple Translation Tables.

answered Jul 18 '13 at 07:16

redd

260
2
8

Thanks - 90% of the rows in my translation table are for the same model though. I may try it anyway if I can't speed things up any other way. – Will Jul 18 '13 at 09:08

score 1 · Answer 2 · edited May 23 '17 at 10:25

Try to shift conditions from WHERE to ON which join table. Generally, there should no difference, but if query optimizer work wrong or ineffective, then earlier conditions reduce amount of rows and so improve performance.

some links:

forget to said, of cause, I mean that you can

`I18n__slug`.`content` = 'category-slug'

move to ON statement.

Something like:

INNER JOIN `fracmerl_dev`.`i18n` AS `I18n__slug`
    ON (
    `I18n__slug`.`content` = 'category-slug'
    AND `Category`.`id` = `I18n__slug`.`foreign_key`
    AND `I18n__slugs`.`model` = 'Category'
    AND `I18n__slug`.`field` = 'slug'
    AND `I18n__slug`.`locale` = 'eng')

Query on large i18n table slow in CakePHP

2 Answers2