Databases
StackOverflow2013
Postsdbo
PONeed assistance optimizing query performance
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONeed assistance optimizing query performance
    text
    copied!<p>I have two queries that are basically the same:</p> <p><em><strong>OLD "TRANSACTIONS" QUERY</em></strong></p> <pre><code>SELECT t.payment_method, t.amount, t.commission FROM `transactions` t, `member_to_group` mtg, `member` m WHERE mtg.`group_id`='37' AND t.`is_deleted`='No' AND t.`member_id`=mtg.`member_id` AND m.member_id=mtg.member_id AND m.`is_root`='No' AND t.`type` IN ('Payment','Credit') </code></pre> <p><em><strong>NEW "PAYMENTS" QUERY</em></strong></p> <pre><code>SELECT p.method, p.amount, p.commission FROM `payments` p, `member_to_group` mtg, `member` m WHERE mtg.`group_id`='37' AND p.`status`='Active' AND p.`member_id`=mtg.`member_id` AND m.member_id=mtg.member_id AND m.`is_root`='No' </code></pre> <p>The first from the "transactions" table, and the second from a newer table called "payments". Basically we had one giant table for every transaction (payments, credits, charges, fees,etc) and it got out of hand because there were always a great deal of fields that weren't used for each type -- so we split the basic types up.</p> <p>For some reason, despite the better organization of the newer table system (all in the same database, mind you), and the similarities between the queries, the older "transaction" query runs much faster. The "transaction" table returns the result in 0.004 seconds while the "payments" query takes 0.303. In some areas on the web application (e.g. listings for multiple groups), this translates into 40 second page load times (as opposed to under 3).</p> <p>Is there a single field (or multi-field) index I might want to use? Can I further optimize the new query?</p> <p>This is running on MySQL 5.0.92</p> <p><strong>EDIT 1:</strong> I currently have single-field indexes on "transactions" (transaction_id, member_id) and "payments" (id, member_id). Unfortunately I only have access to PHPMYADMIN on this particular server and the EXPLAIN output isn't readily copyable. That said, I can see a difference on the "transaction" query in that it says "using where;using index" on the mtg table whereas the "payment" transaction simply says "using where".</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload