Although you're not really asking a question, I presume that you want to
know why the query is very slow and how you speed it up.
> The following query:
>
> CREATE TEMPORARY TABLE orders.recentClickOrders
> SELECT recentHeader.transNum, recentClickLog.adRunId, links.letterId,
> recentHeader.transactionTime
> FROM orders.recentHeader, banner.recentClickLog, mailing_list.links
> WHERE recentHeader.cookie=recentClickLog.cookie
> AND recentClickLog.adRunId=links.adRunId
> AND recentClickLog.cookie IS NOT NULL
>
> explain says:
>
>
+----------------+------+----------------+---------+---------+--------------
----------+------+-------------+
> | table | type | possible_keys | key | key_len | ref
| rows | Extra |
>
+----------------+------+----------------+---------+---------+--------------
----------+------+-------------+
> | recentHeader | ALL | cookie | NULL | NULL | NULL
| 9456 | |
> | recentClickLog | ref | adRunId,cookie | cookie | 256 |
recentHeader.cookie | 5 | Using where |
> | links | ref | adRunId | adRunId | 4 |
recentClickLog.adRunId | 12 |
First of all, MySQL can use one index per table in a join. This means that
it can use either adRunId or cookie from recentClickLog. You can create an
index on adRunId and cookie. If either the first part(s) or all parts of the
index are used MySQL can use this combined index to link to both other
tables.
> describe orders.recentHeader;
> | cookie | varchar(128) | YES | MUL | NULL |
|
>
> describe banner.recentClickLog;
> | cookie | varchar(255) | YES | MUL | NULL | |
As you can see both definitions are not the same. MySQL will have to convert
the values in order to compare them. Although the conversion seems very
trivial in this case, it might prevent MySQL from using the index cookie
from the recentHeader table! I would make both tables varchar(128) as it
seems big enough to hold the cookie data in your case.
> show keys from mailing_list.links;
> +-------------+-----------+-------------+
> | Column_name | Collation | Cardinality |
> +-------------+-----------+-------------+
> | linkId | A | 563158 |
> | adRunId | A | NULL |
> | letterId | A | NULL |
Did you run something like OPTIMIZE TABLE recently on this table? If the
cardinality numbers (they are an estimate of how many unique values are
present for this column) are reasonably accurate, MySQL can optimize the
table order in the query a bit better.
Finally, something that is discouraged by the manual, but sometimes it can
improve the speed of queries quite a bit:
Try and see if the query gets faster when you move the conditions from the
where clause to the joins:
CREATE TEMPORARY TABLE orders.recentClickOrders
SELECT recentHeader.transNum, recentClickLog.adRunId, links.letterId,
recentHeader.transactionTime
FROM orders.recentHeader JOIN banner.recentClickLog
ON recentHeader.cookie=recentClickLog.cookie
AND recentClickLog.cookie IS NOT NULL
JOIN mailing_list.links ON recentClickLog.adRunId=links.adRunId
I won't promise you miracles, but sometimes it helps.
Generally speaking, your efforts should focus on getting the predicted
number of rows in the explain output as low as possible. This results often
in faster queries despite discouraging remarks in the type and extra columns
of the explain output.
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
