If you just reply to this message, and include the entire text of it in the
reply, your reply will go through. However, you should
first review the text of the message to make sure it has something to do
with MySQL. Just typing the word MySQL once will be sufficient, for example.
You have written the following:
Is there a way to get a more detailed explain statement? If not,
maybe someone can answer this for me.
Say I have a statement like this:
EXPLAIN
SELECT /*! STRAIGHT_JOIN */ t1.*
FROM table2 t2
INNER JOIN table1 t1 /*! USE INDEX ( field1 ) */ ON
t1.field1=t2.field1 AND t2.field2 IS NOT NULL
INNER JOIN table3 t3 ON t1.field2=t3.field2 AND t3.field1=1
WHERE MOD(t1.field1,5)=3 AND t1.field3=0 AND t1.field4>NOW();
This produces an explain of
+-------+--------+-----------------------------+---------+---------+-----------+------+-------------------------+
| table | type | possible_keys | key | key_len | ref | rows
|| Extra |
+-------+--------+-----------------------------+---------+---------+-----------+------+-------------------------+
| t2 | index | PRIMARY,field1 | field1 | 7 | NULL | 30
|| where used; Using index |
| t1 | ref | field2,field1,field4,field3 | field1 | 4 | t2.field1 | 508
|| where used |
| t3 | eq_ref | PRIMARY,field1 | PRIMARY | 4 | t1.field2 | 1
|| where used |
+-------+--------+-----------------------------+---------+---------+-----------+------+-------------------------+
That's the exact same explain as (no MOD() expression):
EXPLAIN
SELECT /*! STRAIGHT_JOIN */ t1.*
FROM table2 t2
INNER JOIN table1 t1 /*! USE INDEX ( field1 ) */ ON
t1.field1=t2.field1 AND t2.field2 IS NOT NULL
INNER JOIN table3 t3 ON t1.field2=t3.field2 AND t3.field1=1
WHERE t1.field3=0 AND t1.field4>NOW();
My question is, when is that MOD() performed and used? Is it going
to join t1, t2, and t3, find the fields that match on keys, and
then sift out the rows that don't pass the MOD() or is it going to
join t2 -> t1, sift out the MOD() entries, then join t3 into the
mix on the remaining rows?
sql, query
* Philip Molter
* Texas.net Internet
* http://www.texas.net/
* [EMAIL PROTECTED]
---------------------------------------------------------------------
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
