StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>As a general rule, if your SQL query can fully model what you want to do, then it is likely to be faster than splitting it up into pieces glued together in PHP (or any other language), <em>within certain bounds</em>.</p> <p>Those bounds are:</p> <ol> <li>There must not be strange pathological behavior hiding in MySQL for this case.</li> <li>You must have reasonable indexes on all the necessary columns.</li> <li>There is no (or no <em>likely</em>) case which you can only reasonably detect/handle in PHP in which you would want to abort the query mid-way through.</li> <li>Your result set is not pathologically huge (e.g. it fits in memory and doesn't exceed the size of <code>max_allowed_packet</code> in <code>my.cnf</code>).</li> </ol> <p>Now, this doesn't address whether your SQL (or proposed alternative implementation in PHP) is <em>optimal</em> for what you're doing, but that can only be addressed given more information about what your app does and the end point you're actually trying to reach. It might be fine, it might not.</p> <hr> <p>In a quick glance at your update with the table structure, nothing jumps out at me as the likely cause of a large performance problem, but:</p> <ul> <li>Don't use MyISAM unless you've established you need it. InnoDB is your friend, especially if the tables have a decent amount of write traffic. MyISAM's full-table locks can really bite you. Having FOREIGN KEYS for referential integrity would also be nice.</li> <li><code>action_6_weekly</code> has <code>id</code> as the <code>PRIMARY KEY</code>, and a <code>UNIQUE KEY</code> on... <code>id</code>. This is redundant. <code>PRIMARY KEY</code> is effectively a superset of <code>UNIQUE KEY</code>, you don't need to create a separate <code>UNIQUE KEY</code>.</li> <li>The output of <code>EXPLAIN</code> on your query would be interesting.</li> </ul>

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