StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POJoining and searching multiple MySQL tables with one-to-many relationships
text
Body
copied!<p>I've been furiously googling trying to figure this out, with surprisingly little luck; I would guess this is a common issue.</p> <p>I have 5 tables: orders, addresses, notes, transactions, line_items, and shipments.</p> <p><code>transactions</code>, <code>addresses</code> and <code>notes</code> all have indexed <code>order_id</code> fields - <code>line_items</code> and <code>shipments</code> have indexed <code>transaction_id</code> fields.</p> <p>The best single-query performance I've gotten is completely untenable - above 30 secs sometimes. The great and frustrating irony is that I can do this with a big block of PHP code in under 1. For example, I'll iterate through all the notes to match against a given search, saving all of the order_ids in an array. Then I'll do the same for all of the other tables. Then I'll append a massive IN ( ... ) statement on my final query of the orders table. This works fine, but I know I can do better.</p> <p>The most obvious routes don't work; simply LEFT JOINing all of these tables to the original orders table and GROUPing BY the order.id takes too long - about 9 secs.</p> <p>For the life of me, I can't see how my janky PHP solution is more efficient that mysql doing all of these calculations internally.</p> <p>I've rewritten this so many times, I can hardly recall all the different things I've tried... I think this was my first attempt:</p> <pre><code>SELECT o.id FROM orders o LEFT JOIN addresses a ON a.order_id = o.id LEFT JOIN notes n ON (n.parent_id = o.id AND n.type = "parts") LEFT JOIN transactions t ON t.order_id = o.id LEFT JOIN line_items li ON li.transaction_id = t.id LEFT JOIN shipments s ON s.transaction_id = t.id WHERE 0 = 0 AND ((a.`email` LIKE "%Lachman%" || a.`contact_name` LIKE "%Lachman%" || a.`company_name` LIKE "%Lachman%" || a.`address1` LIKE "%Lachman%" || a.`address2` LIKE "%Lachman%" || a.`country` LIKE "%Lachman%" || a.`city` LIKE "%Lachman%" || a.`region` LIKE "%Lachman%" || a.`postal_code` LIKE "%Lachman%" || n.`note` LIKE "%Lachman%" || t.`g_order_number` LIKE "%Lachman%" || t.`pp_txn_id` LIKE "%Lachman%" || t.`fm_invoice_num` LIKE "%Lachman%" || t.`ebay_item_id` LIKE "%Lachman%" || t.`ebay_buyer_id` LIKE "%Lachman%" || t.`ebay_transaction_id` LIKE "%Lachman%" || t.`ebay_order_id` LIKE "%Lachman%" || li.`partnum` LIKE "%Lachman%" || li.`part_id` LIKE "%Lachman%" || li.`desc` LIKE "%Lachman%" || li.`source` LIKE "%Lachman%" || s.`tracking` LIKE "%Lachman%" || s.`carrier` LIKE "%Lachman%")) GROUP BY o.id ORDER BY `created` DESC </code></pre> <p>2 results 9.6895699501 seconds</p> <p>I'm not sure how accurate the formatting will be on this, but I'll also attached the EXPLAINation:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE o ALL NULL NULL NULL NULL 2840 Using temporary; Using filesort 1 SIMPLE a ref order_id order_id 5 apple_components.o.id 1 1 SIMPLE n ref parent_id,type type 22 const 314 1 SIMPLE t ref order_id order_id 5 apple_components.o.id 1 1 SIMPLE li ref transaction_id transaction_id 4 apple_components.t.id 1 1 SIMPLE s ref transaction_id transaction_id 4 apple_components.t.id 1 Using where </code></pre> <p>Many, many thanks.</p> <p>[Edit: for reference, here is the PHP solution that takes ~0.02s -- how can I do this in straight mysql!?]</p> <pre><code>if ($s['s']) { $search_fields = array( 'a' => array('email', 'contact_name', 'company_name', 'address1', 'address2', 'country', 'city', 'region', 'postal_code'), 'n' => array('note'), 't' => array('g_order_number', 'pp_txn_id', 'fm_invoice_num', 'ebay_item_id', 'ebay_buyer_id', 'ebay_transaction_id', 'ebay_order_id'), 'li' => array('partnum', 'part_id', 'desc', 'source'), 's' => array('tracking', 'carrier') ); $search_clauses = array(); foreach ($search_fields as $table => $fields) { $the_fields = array(); foreach ($fields as $field) $the_fields[] = $table.'.`'.$field.'`'; $clauses = array(); foreach (explode(' ', $s['s']) as $term) $clauses[] = 'CONCAT_WS(" ", '.implode(', ', $the_fields).') LIKE "%'.$term.'%"'; $search_clauses[$table] = $clauses; } $order_ids = array(); $results = mysql_query('SELECT order_id FROM addresses a WHERE '.implode(' AND ', $search_clauses['a'])); while ($result = mysql_fetch_assoc($results)) $order_ids[] = $result['order_id']; $results = mysql_query('SELECT parent_id FROM notes n WHERE type = "orders" AND '.implode(' AND ', $search_clauses['n'])); while ($result = mysql_fetch_assoc($results)) $order_ids[] = $result['parent_id']; $results = mysql_query('SELECT order_id FROM transactions t WHERE '.implode(' AND ', $search_clauses['t'])); while ($result = mysql_fetch_assoc($results)) $order_ids[] = $result['order_id']; $transaction_ids = array(); $results = mysql_query('SELECT transaction_id FROM line_items li WHERE '.implode(' AND ', $search_clauses['li'])); while ($result = mysql_fetch_assoc($results)) $transaction_ids[] = $result['transaction_id']; $results = mysql_query('SELECT transaction_id FROM shipments s WHERE '.implode(' AND ', $search_clauses['s'])); while ($result = mysql_fetch_assoc($results)) $transaction_ids[] = $result['transaction_id']; if (count($transaction_ids)) { $results = mysql_query('SELECT order_id FROM transactions WHERE id IN ('.implode(', ', $transaction_ids).')'); while ($result = mysql_fetch_assoc($results)) if (!empty($result['order_id'])) $order_ids[] = $result['order_id']; } } $query = 'SELECT id FROM orders WHERE id IN ('.implode(', ', $order_ids).')'; </code></pre> <p>2009-10-07: Looking at this again; still haven't found a better solution. The suggestion in the comments to add "FORCE INDEX (PRIMARY)" after "orders o" consistently knocked off a couple of seconds -- but I never really understood why. Also I've since realized there's a limitation in my PHP solution in that searches with multiple terms are only matched within a table instead of across tables.</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