>"explain"
>>
>> Use MySQL's built in "analyze" ("describe"? SQL statement to see what's
>> taking so long).

The statement is called EXPLAIN. You can find it described here:
http://www.mysql.com/doc/E/X/EXPLAIN.html

>> I'm betting on one server having an INDEX on the id or ex fields, and the
>> other not.

If the following fields are missing indexed your query will be slower.
itemid
id
parentid
ex

You can check the indexes on tables using SHOW INDEX FROM tablename.
If the indexes are missing add them with ALTER TABLE ADD INDEX:
http://www.mysql.com/doc/A/L/ALTER_TABLE.html

>> >>> I had been using an INNER JOIN statement for a MySQL query, which
>>> worked fine. However, my new ISP does not have the latest version of
>>> MySQL. The version they have does not support conditions for Inner Join
>>> statements, so I have tried switching to a Left Join instead. However,
>>> the Left Join query takes 12.15 sec (which is obviously far too slow).
>>> The Inner join statement, meanwhile, completed the query in 0.04. Any
>>> idea why??
>>>
>>> Here are the two statements (fast):
>>>
>>> SELECT i.id, i.title, i.description FROM dir_items i INNER JOIN
>>> dir_assoc a ON a.itemid=i.id WHERE a.parentid=59 AND a.ex=0
>>>
>>> Here are the two statements (slow):
>>>
>>> SELECT i.id, i.title, i.description FROM dir_items i LEFT JOIN dir_assoc
>>> a ON a.itemid=i.id WHERE a.parentid=59 AND a.ex=0
>>>

To do an INNER JOIN you simply do
SELECT *
FROM table_a, table_b
WHERE table_a.id = table_b.id
http://www.mysql.com/doc/J/O/JOIN.html

Exchanging an inner (full) join for a left join might not have desired
result.

The MySQL documentation is very good. http://www.mysql.com/doc/index.html

Tina.

-- 
Tina Gunnarsson                                Phone: 408 732 1413
1616 Hollenbeck Ave. #20                       Cell:  406 504 7310
Sunnyvale, CA 94087

   "We only begin to live when we conceive life as Tragedy..."
                                                 - W. B. Yeats


-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to