Problems optimizing a query to run faster

[TeNDoLLA]

Hi, I have a table for word translations and I would need to fetch the original word/translated word -pairs for some language based on users search from the table. I managed to create a query that does it, but the problem is this query takes way too long to execute (around 200ms). I tried removing the BINARY keyword from the query, but then I get the results doubled. Select distinct does not work either because all the rows are distinct. The question is is there a way I could get the same results as with the query below but FASTER. Or is there way to use the query with the BINARY keywords and modify it somehow to run faster?

 

Here is the query

SELECT translations.translation AS translation,
                 translations.translation_id AS translation_id,
                 originals.translation AS original,
                 originals.translation_id AS original_id,
                 translations.locked
        FROM test2_common_translations translations
        JOIN test2_common_translations originals
        ON BINARY translations.constant = BINARY originals.constant
        WHERE translations.lang_id = 1 AND originals.lang_id = 3
        HAVING translation LIKE "%test%" OR original LIKE "%test%"
        ORDER BY original DESC LIMIT 0, 20

 

And here is the table structure

CREATE TABLE `test2_common_translations` (
  `translation_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `lang_id` int(11) UNSIGNED NOT NULL COMMENT 'links to language table',
  `constant` varchar(255) NOT NULL,
  `translation` varchar(255) NOT NULL DEFAULT '',
  `description` varchar(255) NOT NULL DEFAULT '',
  `locked` tinyint(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Shows if the data is locked for editing',
  PRIMARY KEY  (`translation_id`),
  KEY `translation` (`translation`),
  KEY `translation_2` (`translation`,`constant`),
  KEY `i_constant` (`constant`),
  KEY `i_lang_id` (`lang_id`),
  CONSTRAINT `test2_common_translations_ibfk_1` FOREIGN KEY (`lang_id`) REFERENCES `test2_language` (`lang_id`) ON UPDATE CASCADE
) ENGINE=InnoDB;

 

The results what i get now with this slow query is something like this for example translated to finnish language:

translation | translation_id | original | original_id | locked
omena       | 1                  | apple    | 2             | 0
auto          | 3                  | car       | 4             | 0

 

If I remove the BINARY keywords the query runs fast but the result is this:

translation | translation_id | original | original_id | locked
omena       | 1                  | apple    | 2             | 0
auto          | 3                  | apple    | 2             | 0
omena       | 1                  | car       | 4             | 0
auto          | 3                  | car       | 4             | 0

 

I would need to optimize this query somehow and getting the desired results, anyone can help? Would appreciate a lot, thanks.

[The Little Guy]

Run this and post your results:

 

EXPLAIN SELECT translations.translation AS translation,
                 translations.translation_id AS translation_id,
                 originals.translation AS original,
                 originals.translation_id AS original_id,
                 translations.locked
        FROM test2_common_translations translations
        JOIN test2_common_translations originals
        ON BINARY translations.constant = BINARY originals.constant
        WHERE translations.lang_id = 1 AND originals.lang_id = 3
        HAVING translation LIKE "%test%" OR original LIKE "%test%"
        ORDER BY original DESC LIMIT 0, 20

[TeNDoLLA]

Here: http://imageshack.us/photo/my-images/35/explainresults.png/

[The Little Guy]

I don't know if you are doing this or not, but what you really want to do when doing a join, is start with your "main" table and join onto that. Another thing; I see your doing this, but try to remove your having, its like a second query it looks like you can get the same results if it is in your where clause instead.

 

Having is usually only good if you get results back that are not stored in the table. for example:

 

select member_id, sum(amount) as amt, count(*) as total from purchases group by member_id having total > 4 and amt = 1000

 

Next try indexing both of the "constant" columns if they are not.

[TeNDoLLA]

Hmms. It is the same table that holds the translations and the english translation is sued as the "original". So the the "main" table and the joined table are kinda both the main table. I am sure this can be done other way also but I can't just figure it out. Say we have a word car, it has some constant. There is car for 3 different languages and the constant is the same for all the rows.

[TeNDoLLA]

Seems like the problem lies in that there was occurence of the word 'test' in both of the originals and in both of the translations I was testing with, and that is why I get those double results with that query. Tested now with defferent values for example 'car -> auto' and 'carbon -> hiili' and used search  '%car%' and it owrks liek it should. This is the current query now. Dunno then if there will be someday again multiple occurences in both translations and originals, it will probably get fucked up

 

SELECT t.translation_id AS translation_id,
	t.translation AS translation,
	o.translation AS original,
	o.translation_id AS original_id
FROM test2_common_translations t 
JOIN test2_common_translations o ON o.constant = t.constant
WHERE t.lang_id = 3 AND o.lang_id = 1 AND (t.translation LIKE '%car%' OR o.translation LIKE '%car%')

[fenway]

When you use BINARY, you negate index usage.

[TeNDoLLA]

Mkay, thanks. Good to know that.