www.greg-wright.co.uk


mysql> select count(*) fromtransactionHeaders;
+------------------------+
| fromtransactionHeaders |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from transactionHeaders;
+----------+
| count(*) |
+----------+
| 3035 |
+----------+
1 row in set (0.00 sec)

Additional information in an ON Clause can be confusing. Consider the difference between


SELECT books.title, sales.quantity FROM books
LEFT JOIN sales
ON books.bookId = sales.storeId
WHERE sales.storeId = 123


and


SELECT books.title, sales.quantity FROM books
LEFT JOIN sales
ON books.bookId = sales.storeId
AND sales.storeId = 123


Based on http://www.sqlteam.com/article/additional-criteria-in-the-join-clause

The first case does what you would expect - printing out books and sales data for store 123.

Running the following query:

SELECT orderId FROM orders where orderRef=737357947879;

Was taking 2 seconds, despite the key on orders.orderRef
Explain showed it was ignoring the possible key and FORCE INDEX did nothing.

It turned out that orderRef was a varchar (This was not my schema I hasten to point out - storing integers in varchars is definitely a bad idea!)

SELECT orderId FROM orders where orderRef='737357947879';

was almost instantaneous

MySQL was doing a full table scan casting orderRef to an integer at each step then matching with the integer I was passing in.