1

I'm making a dictionary and i have two tables

word (word, description, ext)
translate (id, word_translate, description_tranlate, org_word, language_id)

All words are not translated.I want only search for 'word' and 'word_translated'. The search result should always display word + translated (if exists). My search query looks like this.

SELECT * FROM word
LEFT JOIN translate ON (word=org_word AND language_id=?)
WHERE (word LIKE "%something%" OR translated_word LIKE "%something%")

Query time: 9.3350 sek

But if I only use one Like ex. word LIKE "%something%"

  SELECT * FROM word
    LEFT JOIN translate ON (word=org_word AND language_id=?)
    WHERE (word LIKE "%somethin%")

Query time: 0.0451 sek

And only *word_traslate LIKE* "%somethin%"

SELECT * FROM word
LEFT JOIN translate ON (word=org_word AND language_id=?)
WHERE (translated_word LIKE "%somethin%")

Query time: 0.0037 sek

Why does the query take so much longer with two LIKE's. Is there a beeter way to do this query. Am i doing something wrong?

Aldwoni
  • 1,168
  • 10
  • 24
teebe
  • 21
  • 1
  • Test your queries using `SELECT SQL_NO_CACHE * FROM ...` to make sure that the query cache isn't interfering. You could probably improve general performance by using word IDs instead of the word itself to join the tables. – G-Nugget Feb 28 '13 at 22:36
  • please check my answer below, it'll boost the speed of your query, and accept it if it's the correct answer – Shehabic Mar 01 '13 at 02:14

2 Answers2

3

Move the conditions in WHERE .. into the ON( .. )

SELECT * FROM word
LEFT JOIN translate ON (word=org_word AND language_id=?  
AND (word LIKE "%somethin%" OR translated_word LIKE "%somethin%") )
Shehabic
  • 6,787
  • 9
  • 52
  • 93
  • 1
    +1 Even though I had to read this three times before I understood what you were doing. – Pieter Geerkens Mar 01 '13 at 01:34
  • 2
    @PieterGeerkens the JOIN happens before the filtration via WHERE command, so to reduce the time consumed and the amount of data created in the virtual table for the JOIN statement we add all conditions to the joining condition. – Shehabic Mar 01 '13 at 02:17
  • Yep, got that on the third reading. For some reason it didn't click right away. Good explanation gets another +1 – Pieter Geerkens Mar 01 '13 at 04:27
  • @user2121417 still my answer is correct, you have to mark it correct, it reduces the time more than sub-queries even. – Shehabic Mar 01 '13 at 23:08
  • Sorry for my bad explanation, (english). But thanks for the tips. It helped with sub queries: `'SELECT * FROM word LEFT JOIN (SELECT * FROM translate WHERE language_id=? AND org_word LIKE '%something%' ) as translated ON svenska=word WHERE word LIKE '%a%' or translated LIKE '%a%''` **Query time:0.0080 sek sek)** – teebe Mar 01 '13 at 23:30
  • i never knew you could do (... AND ...) in ON... i certainly have more to learn! – Christian Noel Mar 29 '17 at 08:51
0

Note that any phrase such as LIKE '%something%' by necessity does a table scan (because of the leading %). Do as much filtering as possible before performing this and do as much joining as possible after performing this to reduce the impact of the table scan on your performance. Use sub-queries if you need to get control over sequencing. The optimizer will pick up some of this, but it never hurts to help it out.

G-Nugget
  • 8,666
  • 1
  • 24
  • 31
Pieter Geerkens
  • 11,775
  • 2
  • 32
  • 52