0

I need to optimise SQL queries in my PHP code used by hugely over headed web service. I'am getting list of words.

1) get word identifiers

2) foreach identifier get word as php object

3) print word details in xml

Now I have a code that takes this identifier of Word in constructor. Then when user accesses properties is lazily loading given properties. Web service is returning all word details so it takes all word properties. Making many 5-10 simple sql queries each time like get native word, get foreign word, get transcription. It was done as such assuming that I one time need less info about word and the other time I need more information.

Now when my hosting provider deactivated my website as impacting too much overhead on resources of shared hosting I need to optimize it. 1) I will provide INDEX, UNIQUE where it is possible and it has't been used yet. 2) I think about replacing many simple sql queries lazily retrieving word's properties with longer joining query: 

EXPLAIN SELECT nt.deutsch_id, nt.article, n.deutsch_word, ft.french_id, ft.article, f.french_word, p.part, fd.transcription, fd.definition
FROM translation_enfr ft
INNER JOIN translation_ende nt ON nt.translation_id = ft.translation_id
INNER JOIN deutsch n ON n.deutsch_id = nt.deutsch_id
INNER JOIN french f ON f.french_id = ft.french_id
INNER JOIN parts p ON p.part_id = ft.part_id
LEFT JOIN french_details fd ON fd.translation_id = ft.translation_id
WHERE ft.translation_id =2

Do you think it will be better/faster than using: 

public function getNativeWord($withArticle = true) { 
                if(is_null($this->nativeWord)) { 
                 $q = "SELECT {$langLabel}_word FROM {$langLabel} 
                       WHERE {$langLabel}_id = :native_id";
                } 
}

And other current queries are similar: 

"SELECT {$langLabel}_word FROM {$langLabel} WHERE {$langLabel}_id = :foreign_id"
"SELECT article FROM translation_en{$this->nativeLang} WHERE translation_id = :translation_id";
"SELECT parts.part FROM parts INNER JOIN translation ON parts.part_id = translation.part_id WHERE translation_id = :trans_id"
"SELECT transcription FROM {$langLabel}_details WHERE translation_id = :trans_id";
"SELECT definition FROM {$langLabel}_details WHERE translation_id = :trans_id";

I think to preload each properties of this Word object only remaining to load easily images, sentences, comments as here I have 1toMany relationship!

UPDATED 1

Output of EXPLAIN SELECT...*

explain in mysql

Michał Ziobro
  • 10,759
  • 11
  • 88
  • 143
  • `SHOW CREATE TABLE` -- possibly missing some important indexes. – Rick James Feb 09 '16 at 23:36
  • Why have separate tables for `french` and `deutsch`? – Rick James Feb 09 '16 at 23:37
  • I have this table normalized as I think. french, english, deutsch, spanish, etc. about 10 languages. Than translation_enfr, translation_enes, translation_ende, etc. joining them and french_details, english_details, separate for images and sentences... – Michał Ziobro Feb 09 '16 at 23:40
  • Some words in one language may have different meanings (translations) in other language... mama (PL) => mum (or mom) (EN) so I have them only once in polish table and english table and in translation enpl I have their joining + article, part of speeach etc. Joining between french and spanish is through translation_enes and translation_enfr tables. – Michał Ziobro Feb 09 '16 at 23:43
  • Where's the output of the `EXPLAIN`? – Rick James Feb 09 '16 at 23:47
  • Normalization stresses logical groupings of data elements. You could store ALL languages in one table, ALL translations in another table with a column indicator for type. – Parfait Feb 10 '16 at 01:33
  • But how could i put all languages in one table as it isn't 1 to 1 relationship! This will require duplicated and will be hard to manage. – Michał Ziobro Feb 10 '16 at 09:51
  • This new query with joins also is slow. My website was reactivated and in the logs I can see it is there maybe it is less slow queries as this reduces 5 other simple queries. – Michał Ziobro Feb 10 '16 at 09:53

0 Answers0