Hi, Do anyone know there may be a limitation in mysql to do a 15 tables equi-join?
I did some experiment with it if I only do 10 tables equi-join, it takes seconds to retrieve the results, but if I do more than 10 tables, it took forever and run with 100% CPU time to execute the query. The tables I am trying to join together do NOT contain more than 100 records in each of them (the whole TEST database only 1.7M). So the table size is not a problem at all. Plus I am running this query in a 512M memory Linux box. It should handle it. The following is the query I tried to run: select sum(RPT_SPEND_AMT) FROM FACT_SPEND_TRANSACTION_MONTH A, FLATTENED_COMMODITY B, FLATTENED_GL_ACCOUNT C, FLATTENED_COST_CENTER D, FLATTENED_GEOGRAPHY E, FLATTENED_VENDOR F, FLATTENED_AP_SYSTEM G, FLATTENED_VENDOR_APPROVAL H, FLATTENED_CPC_CAPABLE I, FLATTENED_INVOICE_DATE_RANGE J, FLATTENED_PAYMENT_METHOD K, FLATTENED_REQUISITION_TYPE L, FLATTENED_VENDOR_SPEND_GROUP M, FLATTENED_TRANSACTION_RANGE_COUNT N, FLATTENED_TRANSACTION_RANGE_SPEND O WHERE A.COMMODITY_ID = B.COMMODITY_ID AND A.GL_ACCOUNT_ID = C.GL_ACCOUNT_ID AND A.COST_CENTER_ID = D.COST_CENTER_ID AND A.GEOGRAPHY_ID = E.GEOGRAPHY_ID AND A.VENDOR_ID = F.VENDOR_ID AND A.AP_SYSTEM_ID = G.AP_SYSTEM_ID AND A.VENDOR_APPROVAL_ID = H.VENDOR_APPROVAL_ID AND A.CPC_CAPABLE_ID = I.CPC_CAPABLE_ID AND A.INVOICE_DATE_RANGE_ID = J.INVOICE_DATE_RANGE_ID AND A.PAYMENT_METHOD_ID = K.PAYMENT_METHOD_ID AND A.REQUISITION_TYPE_ID = L.REQUISITION_TYPE_ID AND A.VENDOR_SPEND_GROUP_ID = M.VENDOR_SPEND_GROUP_ID AND A.TRANSACTION_RANGE_COUNT_ID = N.TRANSACTION_RANGE_COUNT_ID AND A.TRANSACTION_RANGE_SPEND_ID = O.TRANSACTION_RANGE_SPEND_ID; --------------------------------------------------------------------- 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
