Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>For a start, if you want to optimize a SQL Statement, first get rid of that HTML crap in there. If nothing else, it obfuscates the actual structure of the Statement. If you must, you can put it back at the end of your optimization, although I'd seriously vote against it: You've got PHP to do the markup. For the sake of clarity I've made it a habit to use JOIN clauses, I rephrased the whole Thing accordingly. </p> <p>What this process gave me was this:</p> <pre><code>SELECT SQL_CALC_FOUND_ROWS, o.id, o.created_timestamp, o.status, d.id, d.first_name, d.last_name, p.id, p.first_name, p.last_name, p.ssn GROUP_CONCAT(t.name SEPARATOR ', '), FROM `orders` o JOIN `doctors` d ON d.id = o.doctor_id JOIN `patients` p ON p.id = o.patient_id JOIN `users` u ON u.username = o.assigned_username JOIN `events` e ON e.event_id = o.event_id JOIN `tests_ordered` tst ON tst.order_id = o.id JOIN `tests` t ON t.id = tst.test_id WHERE o.status='Complete' OR o.status='Draft' GROUP BY o.id </code></pre> <p>There are a few things to be observed here:</p> <p><em>1)</em> Your main table is <code>orders</code>. That is also the one you're using the WHERE clause for AND you're GROUPing BY that. With <code>id</code> as primary index and a second index on <code>status</code>, this shouldn't be too bad.</p> <p><em>2)</em> You're linking in four other tables through was I assume to be foreign keys. Two of those tables are never actually used: there is (most probably) no need to join on <code>users</code>and <code>events</code> at all. You should get rid of those, which incidentally also removes the less-than-stellar join on a text column (username). Make sure the remaining tables <code>doctors</code> and <code>patients</code> have their primary keys on their respective <code>id</code> columns.</p> <p><em>3)</em> You have a more complex join on two tables <code>tests_ordered</code> and <code>tests</code>. All that does is give you a concatenated string of names, but it does add the complexity of the GROUP BY clause. There are two ways to go from here: try to optimize those joins or remove them form the select entirely.</p> <p><em>3) Solution A</em> To optimize those joins, make sure you've got an index on <code>tests_ordered.order_id</code> and on <code>tests_ordered.test_id</code> and a Primary index on <code>tests.id</code>. Your Statement should look like this:</p> <pre><code>SELECT SQL_CALC_FOUND_ROWS, o.id, o.created_timestamp, o.status, d.id, d.first_name, d.last_name, p.id, p.first_name, p.last_name, p.ssn GROUP_CONCAT(t.name SEPARATOR ', '), FROM `orders` o JOIN `doctors` d ON d.id = o.doctor_id JOIN `patients` p ON p.id = o.patient_id JOIN `tests_ordered` tst ON tst.order_id = o.id JOIN `tests` t ON t.id = tst.test_id WHERE o.status='Complete' OR o.status='Draft' GROUP BY o.id </code></pre> <p><em>3) Solution B</em> Remove the entire tests/tests_ordered stuff, and put it in a separate select. Your main select will now look like this:</p> <pre><code>SELECT SQL_CALC_FOUND_ROWS, o.id, o.created_timestamp, o.status, d.id, d.first_name, d.last_name, p.id, p.first_name, p.last_name, p.ssn FROM `orders` o JOIN `doctors` d ON d.id = o.doctor_id JOIN `patients` p ON p.id = o.patient_id WHERE o.status='Complete' OR o.status='Draft' </code></pre> <p>But you'll either have to run an additional SELECT per row to get the concatenated t.name or you do just one SELECT for all order ids on the current page. The latter would look like this:</p> <pre><code>SELECT o.id, GROUP_CONCAT(t.name SEPARATOR ', '), FROM `orders` o JOIN `tests_ordered` tst ON tst.order_id = o.id JOIN `tests` t ON t.id = tst.test_id WHERE o.in IN ( &lt;put the 10 ids on your current page here, separated by commas&gt; ) GROUP BY o.id </code></pre> <p>Solution A should run pretty quick on a decent machine. Solution B should run pretty quick on any machine. Straight JOINS on indexed foreign keys are cheap.</p> <p>4) None of the above selects should return 642 million rows on a database that has less than 642 million orders in it, which I assume you don't. Since MySQL tells you that it probably had to build a full cartesian product, which also explains the experienced speed. That means one of your straight foreign key joins went haywire. Most likely the one in question is the join on <code>users</code> - a useless join in the first place, but in any case: check the uniqueness of its <code>username</code> column.</p>
    singulars
    1. This table or related slice is empty.
    1. PODataTables: Speed up server side processing with multiple tables, grouping, and html content?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USHazzit
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODataTables: Speed up server side processing with multiple tables, grouping, and html content?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COthanks for steering me in the right direction. the problem was with the unused tables, removing users and events tables solved the problem. Simply doing that dropped the time for the query down to .0035 seconds from 20+ seconds. I'd prefer to use joins and not use so much html in the query, but DataTables fails to work correctly then. I'll post the fixed code shortly.
      singulars
      1. PO
      1. UScompcentral
    2. COBy the way, none of the SQL statements that you posted actually work.. minor syntax errors. There is no comma needed after SQL_CALC_FOUND_ROWS. Using joins, the query would look like what I have in my updated post.
      singulars
      1. PO
      1. UScompcentral
    3. COLacking a copy of your database structure, I wrote the SQL statements from the back of my head. So yes, they are very likely to have minor syntax errors - feel free to edit my post accordingly. About the HTML in the query, you are right, you need to add it somewhere - but doing so in SQL is just a weird (and expensive) way of doing so, you've got PHP for that.
      singulars
      1. PO
      1. USHazzit
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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