Thank you for your detailed response.
You might get better performance just from using the explicit INNER JOINS but I make no assumptions.
I tried INNER JOINS and did not see any difference in speed.
You may also get better performance if you had composite indexes (not just several individual field indexes) on the tables that contain all of the columns used in each JOIN clause. For example you could have an index on user_intros with (user_id, lang_id) and the engine won't have to read the table to check for either condition as they would both be integer values that exist in an index.
I am already using composite indexes for every table with a lang_id field, like user_intros.
ALSO NOTE: there is no condition in the ON clause of loc_countries_lang that relates that table to any other. This means that for all values in the veg table you will need to match one row from the loc_countries_lang table that has lang_id=0. If there are more than one languages that match that key, you will get multiple sets of matches.
Well, I was thinking, since this table will never really change, and there only a couple of hundred entries, I should just store this as an array and get the country name directly from the array. However, when I removed the loc_countries_lang table from the query, it was still slow, i.e. 3-5 seconds on production server.
Would changing the LEFT JOIN to an INNER JOIN improve things? It would be possible to change the logic such that the veg_titles table is used in an INNER JOIN instead, but when I tried that it was still very slow (3-5 seconds). In this instance the EXPLAIN returned the following:
t ALL veg_lang_id NULL NULL NULL 76001 Using where; Using temporary; Using filesort
Every other table in the EXPLAIN returned one row with type eq_ref
Please, let me know if I helped or not, OK?
Yes, thank you for your response, however the respone time is still too slow. Now I'm thinking that maybe my underlying database structure is not correct. Am I joining too many tables? Is there anything else I can do before I try increasing the sort_buffer? Thanks.
_________________________________________________________________
MSN Toolbar provides one-click access to Hotmail from any Web page – FREE download! http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
