Databases
StackOverflow2013
Postsdbo
PODataTables: Speed up server side processing with multiple tables, grouping, and html content?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODataTables: Speed up server side processing with multiple tables, grouping, and html content?
    text
    copied!<p>I'm using datatables to display data from multiple mySQL tables (7 of them actually). Currently there really isn't much data, but I see "Showing 1 to 7 of 7 entries (filtered from 642,660,480 total entries)." and it takes 20 seconds to display only 7 entries. Once I really start adding a lot of content to the database, I'm sure this is going to be unusable.</p> <p>I'm sure that there are better ways to accomplish what I'm trying to do, but this is the only way I've been able to get it working.</p> <p>Here is my server side script:</p> <pre><code>$q1 = "'"; $q2 = '"'; $order_id = "CONCAT( ".$q2."&lt;input type='hidden' id='order_id' value='".$q2.", o.id, ".$q2."'&gt;&lt;a href='order_details.php?id=".$q2.", o.id, ".$q2."'&gt;&lt;img src='https://pas.greysignal.com/img/search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;".$q2.", o.id )"; $patient_name = "CONCAT( ".$q2."&lt;input type='hidden' id='patient_name' value='".$q2.", p.first_name, ' ', p.last_name, ".$q2."'&gt;&lt;input type='hidden' id='patient_id' value='".$q2.", p.id, ".$q2."'&gt;&lt;input type='hidden' id='patient_ssn' value='".$q2.", p.ssn, ".$q2."'&gt;&lt;a href='patient_details.php?id=".$q2.", p.id, ".$q2."'&gt;&lt;img src='https://pas.greysignal.com/img/search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;".$q2.", p.first_name, ' ', p.last_name )"; $doc_name = "CONCAT( ".$q2."&lt;input type='hidden' id='doctor_name' value='".$q2.", d.first_name, ' ', d.last_name, ".$q2."'&gt;&lt;input type='hidden' id='doctor_id' value='".$q2.", d.id, ".$q2."'&gt;&lt;a href='doctor_details.php?id=".$q2.", d.id, ".$q2."'&gt;&lt;img src='https://pas.greysignal.com/img/search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;".$q2.", d.first_name, ' ', d.last_name )"; $order_date = "FROM_UNIXTIME(o.created_timestamp, '%m/%e/%Y')"; $tests = "GROUP_CONCAT(t.name SEPARATOR ', ')"; $aColumns = array($order_id, $order_date, $doc_name, $patient_name, $tests, 'o.status'); /* Indexed column (used for fast and accurate table cardinality) */ $sIndexColumn = "o.id"; /* DB table to use */ $sTable = "`orders` o, `patients` p, `doctors` d, `tests_ordered` tst, `tests` t, `users` u, `events` e"; $sWhere = "WHERE p.id = o.patient_id AND d.id = o.doctor_id AND tst.order_id = o.id AND t.id = tst.test_id AND u.username = o.assigned_username AND e.event_id = o.event_id"; $order_status = isset($_GET['status']) ? $_GET['status'] : 'all'; if($order_status == 'all'){ }else{ $sWhere .= " AND (o.status='Complete' OR o.status='$order_status')"; } $sGroupBy = "GROUP BY o.id"; /* Database connection information */ $gaSql['user'] = DB_USER; $gaSql['password'] = DB_PASSWORD; $gaSql['db'] = DB_NAME; $gaSql['server'] = DB_SERVER; /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * If you just want to use the basic configuration for DataTables with PHP server-side, there is * no need to edit below this line */ /* * MySQL connection */ $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or die( 'Could not open connection to server' ); mysql_select_db( $gaSql['db'], $gaSql['link'] ) or die( 'Could not select database '. $gaSql['db'] ); /* * Paging */ $sLimit = ""; if ( isset( $_GET['iDisplayStart'] ) &amp;&amp; $_GET['iDisplayLength'] != '-1' ) { $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ". mysql_real_escape_string( $_GET['iDisplayLength'] ); } /* * Ordering */ if ( isset( $_GET['iSortCol_0'] ) ) { $sOrder = "ORDER BY "; for ( $i=0 ; $i&lt;intval( $_GET['iSortingCols'] ) ; $i++ ) { if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) { $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]." ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", "; } } $sOrder = substr_replace( $sOrder, "", -2 ); if ( $sOrder == "ORDER BY" ) { $sOrder = ""; } } /* * Filtering * NOTE this does not match the built-in DataTables filtering which does it * word by word on any field. It's possible to do here, but concerned about efficiency * on very large tables, and MySQL's regex functionality is very limited */ if ( $_GET['sSearch'] != "" ) { $sWhere .= " AND ("; for ( $i=0 ; $i&lt;count($aColumns) ; $i++ ) { if($i!=4){ //skip tests column $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR "; } } $sWhere = substr_replace( $sWhere, "", -3 ); $sWhere .= ')'; } /* Individual column filtering */ for ( $i=0 ; $i&lt;count($aColumns) ; $i++ ) { if($i!=4){ //skip tests column if ( $_GET['bSearchable_'.$i] == "true" &amp;&amp; $_GET['sSearch_'.$i] != '' ) { if ( $sWhere == "" ) { $sWhere = "WHERE "; } else { $sWhere .= " AND "; } $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' "; } } } /* * SQL queries * Get data to display */ $sQuery = " SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))." FROM $sTable $sWhere $sGroupBy $sOrder $sLimit "; //echo $sQuery; //die(); $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); /* Data set length after filtering */ $sQuery = " SELECT FOUND_ROWS() "; $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal); $iFilteredTotal = $aResultFilterTotal[0]; /* Total data set length */ $sQuery = " SELECT COUNT(".$sIndexColumn.") FROM $sTable "; $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); $aResultTotal = mysql_fetch_array($rResultTotal); $iTotal = $aResultTotal[0]; //added to hide filtering //$iTotal = $iFilteredTotal; /* * Output */ $output = array( "sEcho" =&gt; intval($_GET['sEcho']), "iTotalRecords" =&gt; $iTotal, "iTotalDisplayRecords" =&gt; $iFilteredTotal, "aaData" =&gt; array() ); while ( $aRow = mysql_fetch_array( $rResult ) ) { $row = array(); for ( $i=0 ; $i&lt;count($aColumns) ; $i++ ) { if ( $aColumns[$i] != ' ' ) { /* General output */ $row[] = $aRow[$i]; } } $output['aaData'][] = $row; } echo json_encode( $output ); </code></pre> <p>Here is the query that the server side script is generating:</p> <pre><code>SELECT SQL_CALC_FOUND_ROWS CONCAT( "&lt;input type='hidden' id='order_id' value='", o.id, "'&gt;&lt;a href='order_details.php?id=", o.id, "'&gt;&lt;img src='search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;", o.id ), FROM_UNIXTIME(o.created_timestamp, '%m/%e/%Y'), CONCAT( "&lt;input type='hidden' id='doctor_name' value='", d.first_name, ' ', d.last_name, "'&gt;&lt;input type='hidden' id='doctor_id' value='", d.id, "'&gt;&lt;a href='doctor_details.php?id=", d.id, "'&gt;&lt;img src='search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;", d.first_name, ' ', d.last_name ), CONCAT( "&lt;input type='hidden' id='patient_name' value='", p.first_name, ' ', p.last_name, "'&gt;&lt;input type='hidden' id='patient_id' value='", p.id, "'&gt;&lt;input type='hidden' id='patient_ssn' value='", p.ssn, "'&gt;&lt;a href='patient_details.php?id=", p.id, "'&gt;&lt;img src='search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;", p.first_name, ' ', p.last_name ), GROUP_CONCAT(t.name SEPARATOR ', '), o.status FROM `orders` o, `patients` p, `doctors` d, `tests_ordered` tst, `tests` t, `users` u, `events` e WHERE p.id = o.patient_id AND d.id = o.doctor_id AND tst.order_id = o.id AND t.id = tst.test_id AND u.username = o.assigned_username AND e.event_id = o.event_id AND (o.status='Complete' OR o.status='Draft') GROUP BY o.id </code></pre> <p>I'm trying to determine what I can do to optimize this without breaking the search and sorting features of datatables. I've created indexes and set primary keys for all tables as best as I know how. Is there a way to use a JOIN maybe?</p> <p>Here is the output of EXPLAIN statement:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE u index NULL PRIMARY 32 NULL 5 Using index; Using temporary; Using filesort 1 SIMPLE o ALL PRIMARY,patient_id,doctor_id,event_id,assigned_use... NULL NULL NULL 6 Using where 1 SIMPLE d eq_ref PRIMARY PRIMARY 4 pasdbadmin.o.doctor_id 1 1 SIMPLE e eq_ref PRIMARY PRIMARY 4 pasdbadmin.o.event_id 1 Using index 1 SIMPLE tst ref order_id,test_id order_id 4 pasdbadmin.o.id 1 1 SIMPLE t eq_ref PRIMARY PRIMARY 4 pasdbadmin.tst.test_id 1 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 pasdbadmin.o.patient_id 1 </code></pre> <p><strong>UPDATE:</strong></p> <p>The problem seems to have been an issue with using including the users and events tables in the query (neither of which were actually used). Here is the revised code that executes much faster:</p> <pre><code>$q1 = "'"; $q2 = '"'; $order_id = "CONCAT( ".$q2."&lt;input type='hidden' id='order_id' value='".$q2.", o.id, ".$q2."'&gt;&lt;a href='order_details.php?id=".$q2.", o.id, ".$q2."'&gt;&lt;img src='https://pas.greysignal.com/img/search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;".$q2.", o.id )"; $patient_name = "CONCAT( ".$q2."&lt;input type='hidden' id='patient_name' value='".$q2.", p.first_name, ' ', p.last_name, ".$q2."'&gt;&lt;input type='hidden' id='patient_id' value='".$q2.", p.id, ".$q2."'&gt;&lt;input type='hidden' id='patient_ssn' value='".$q2.", p.ssn, ".$q2."'&gt;&lt;a href='patient_details.php?id=".$q2.", p.id, ".$q2."'&gt;&lt;img src='https://pas.greysignal.com/img/search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;".$q2.", p.first_name, ' ', p.last_name )"; $doc_name = "CONCAT( ".$q2."&lt;input type='hidden' id='doctor_name' value='".$q2.", d.first_name, ' ', d.last_name, ".$q2."'&gt;&lt;input type='hidden' id='doctor_id' value='".$q2.", d.id, ".$q2."'&gt;&lt;a href='doctor_details.php?id=".$q2.", d.id, ".$q2."'&gt;&lt;img src='https://pas.greysignal.com/img/search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;".$q2.", d.first_name, ' ', d.last_name )"; $order_date = "FROM_UNIXTIME(o.created_timestamp, '%m/%e/%Y')"; $tests = "GROUP_CONCAT(t.name SEPARATOR ', ')"; $aColumns = array($order_id, $order_date, $doc_name, $patient_name, $tests, 'o.status'); /* Indexed column (used for fast and accurate table cardinality) */ $sIndexColumn = "o.id"; /* DB table to use */ $sTable = "`orders` o, `patients` p, `doctors` d, `tests_ordered` tst, `tests` t"; $sWhere = "WHERE p.id = o.patient_id AND d.id = o.doctor_id AND tst.order_id = o.id AND t.id = tst.test_id"; $order_status = isset($_GET['status']) ? $_GET['status'] : 'all'; if($order_status == 'all'){ }else{ $sWhere .= " AND (o.status='Complete' OR o.status='$order_status')"; } $sJoin = ""; $sGroupBy = "GROUP BY o.id"; /* Database connection information */ $gaSql['user'] = DB_USER; $gaSql['password'] = DB_PASSWORD; $gaSql['db'] = DB_NAME; $gaSql['server'] = DB_SERVER; /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * If you just want to use the basic configuration for DataTables with PHP server-side, there is * no need to edit below this line */ /* * MySQL connection */ $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or die( 'Could not open connection to server' ); mysql_select_db( $gaSql['db'], $gaSql['link'] ) or die( 'Could not select database '. $gaSql['db'] ); /* * Paging */ $sLimit = ""; if ( isset( $_GET['iDisplayStart'] ) &amp;&amp; $_GET['iDisplayLength'] != '-1' ) { $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ". mysql_real_escape_string( $_GET['iDisplayLength'] ); } /* * Ordering */ if ( isset( $_GET['iSortCol_0'] ) ) { $sOrder = "ORDER BY "; for ( $i=0 ; $i&lt;intval( $_GET['iSortingCols'] ) ; $i++ ) { if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" ) { $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]." ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", "; } } $sOrder = substr_replace( $sOrder, "", -2 ); if ( $sOrder == "ORDER BY" ) { $sOrder = ""; } } /* * Filtering * NOTE this does not match the built-in DataTables filtering which does it * word by word on any field. It's possible to do here, but concerned about efficiency * on very large tables, and MySQL's regex functionality is very limited */ if ( $_GET['sSearch'] != "" ) { $sWhere .= " AND ("; for ( $i=0 ; $i&lt;count($aColumns) ; $i++ ) { if($i!=4){ //skip tests column $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR "; } } $sWhere = substr_replace( $sWhere, "", -3 ); $sWhere .= ')'; } /* Individual column filtering */ for ( $i=0 ; $i&lt;count($aColumns) ; $i++ ) { if($i!=4){ //skip tests column if ( $_GET['bSearchable_'.$i] == "true" &amp;&amp; $_GET['sSearch_'.$i] != '' ) { if ( $sWhere == "" ) { $sWhere = "WHERE "; } else { $sWhere .= " AND "; } $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' "; } } } /* * SQL queries * Get data to display */ $sQuery = " SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))." FROM $sTable $sWhere $sJoin $sGroupBy $sOrder $sLimit "; $filename = __DIR__.DIRECTORY_SEPARATOR."sql_log.txt"; file_put_contents($filename, $sQuery, FILE_APPEND); $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); /* Data set length after filtering */ $sQuery = " SELECT FOUND_ROWS() "; $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal); $iFilteredTotal = $aResultFilterTotal[0]; /* Total data set length */ $sQuery = " SELECT COUNT(".$sIndexColumn.") FROM $sTable "; $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error()); $aResultTotal = mysql_fetch_array($rResultTotal); $iTotal = $aResultTotal[0]; //added to hide filtering //$iTotal = $iFilteredTotal; /* * Output */ $output = array( "sEcho" =&gt; intval($_GET['sEcho']), "iTotalRecords" =&gt; $iTotal, "iTotalDisplayRecords" =&gt; $iFilteredTotal, "aaData" =&gt; array() ); while ( $aRow = mysql_fetch_array( $rResult ) ) { $row = array(); for ( $i=0 ; $i&lt;count($aColumns) ; $i++ ) { if ( $aColumns[$i] != ' ' ) { /* General output */ $row[] = $aRow[$i]; } } $output['aaData'][] = $row; } echo json_encode( $output ); </code></pre> <p>If I were to use JOINS, the updated query would be:</p> <pre><code> SELECT SQL_CALC_FOUND_ROWS CONCAT( "&lt;input type='hidden' id='order_id' value='", o.id, "'&gt;&lt;a href='order_details.php?id=", o.id, "'&gt;&lt;img src='search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;", o.id ), FROM_UNIXTIME(o.created_timestamp, '%m/%e/%Y'), CONCAT( "&lt;input type='hidden' id='doctor_name' value='", d.first_name, ' ', d.last_name, "'&gt;&lt;input type='hidden' id='doctor_id' value='", d.id, "'&gt;&lt;a href='doctor_details.php?id=", d.id, "'&gt;&lt;img src='search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;", d.first_name, ' ', d.last_name ), CONCAT( "&lt;input type='hidden' id='patient_name' value='", p.first_name, ' ', p.last_name, "'&gt;&lt;input type='hidden' id='patient_id' value='", p.id, "'&gt;&lt;input type='hidden' id='patient_ssn' value='", p.ssn, "'&gt;&lt;a href='patient_details.php?id=", p.id, "'&gt;&lt;img src='search.png' border='0'&gt;&lt;/a&gt; &amp;nbsp;", p.first_name, ' ', p.last_name ), GROUP_CONCAT(t.name SEPARATOR ', '), o.status 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>The problem with this is that DataTables just isn't designed to function correctly with JOINS when sorting and filtering is used due to the columns array, etc. I would love to see a solution that would work with a query like this though.</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