* Stig Nørgaard Jepsen > > select distinct > > t1.textkey, > > if(t2.textid,t2.languageid,t3.languageid) as languageid, > > if(t2.textid,t2.textid,t3.textid) as textid, > > if(t2.textid,t2.textvalue,t3.textvalue) as textvalue > > from texts t1 > > left join texts t2 on > > t2.textkey=t1.textkey and > > t2.languageid='da' > > left join texts t3 on > > t3.textkey=t1.textkey and > > t3.languageid='en'; > > > > I don't know if you always have at least one of 'da' or 'en' for every > > possible textkey, and what behaviour you would like if you don't. If you > > always have at least one of them, the statement could be simplified. > > Do you mean something like this then: > select distinct > t1.textkey, > if(t2.textid,t2.languageid,t1.languageid) as languageid, > if(t2.textid,t2.textid,t1.textid) as textid, > if(t2.textid,t2.textvalue,t1.textvalue) as textvalue > from texts t1 > left join texts t2 on > t2.textkey=t1.textkey and > t2.languageid='da' > WHERE t1.languageid='da' or t1.languageid='en'
yes :) > I would emagine that only one 'left join' takes less processing power? Yes, but I don't think there is very much extra processing time involved using one more join in most cases, as long as the join is indexed and the index blocks involved are cached, or if the amount of data is reasonable small... you are not translating the entire language, only a few words, right? If the size of _all_ used indexes on the server (combined index field widths multiplied by number of records for all tables in all databases!) is very big compared to the available memory on the server (key_buffer_size), the mysql key buffer will more often miss the needed keys, and the server will need to read from disk. When there are large amounts af data involved (multiple GB) and many joins (10-15), I have experienced a high processing time the first time I execute a query (like 30 seconds), and when the same statement is repeated I get an execution time of less than a second (version 3.23, MyIsam). All the joins use indexes. I suppose in such a case (too small key_buffer_size and very big index) one less join could be helpfull. > One thing that I have been wondering about, is if such a query > like this, is too power-hungry if it has to be run every time you > do anything on a homepage. That depends... what is your experience? Do you need to read _all_ textkeys for each page? How many different textkeys do you have in the table? > One possibility is to cache the result in a session. Yes... if you need to do the same query for every click, and this query could possibly be time consuming, you should probably reconsider. Different languages on a web site could for instance be implemented by using multiple template files: somepage_da.php, somepage_en.php and so on, combined with joining from the language tables when listing information from the database. The different php-files could be produced by a script, using the same language tables and a single, language neutral template... This is just a suggestion, there are many ways to do this. :) > Merry christmas from > Stig in Denmark! Takk, det samme! Roger, .no --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
