StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In complement to @azzit nice answer i can give you some hints that we've used on datatables server side queries:</p> <ul> <li>avoid <strong>group concat</strong>, this will always makes your queries very slower, even maybe using temporary tables, a thing you should avoid. So instead wait for your paginated result and launch extra data (like here the tests_ordered data) based on the actual identifiers returned after pagination of the main query</li> <li>solution used above could became problematic if you have to filter the main query on tests_ordered elements. But in fact the solution is to add <strong>EXISTS</strong> subselects to filters you main query on theses 1->n related tables (and with exists subqueries you will not need theses tables in your main query, only on subselect when filter applied)</li> <li>Make one <strong>count query</strong> with needed joins and conditions, and try to record results while filters are not modified (you can ignore order by modifications).</li> <li>watch out for <strong>order by</strong>, <strong>you should not allow all columns to be used with order by</strong>. Order by will get your query very slow if an unindexed column is used. Especially you should not allow order by on 'multiple values' columns like tests_ordered.</li> <li>If you have a big number of joins and you think your query is written in the right order, use <strong>STRAIGHT_JOIN</strong> on the select to avoid loosing seconds in optimizer computations (heppen with really big number of joins).</li> <li>Use left joins for columns that could be empty (null), but try to avoid putting them in the query if this data is not used in order by, a filter, or not in a visible column.</li> <li>test your query with <strong>explain</strong>, do not hesitate to add variations on filters and oreder by. You will see that using functions like FROM_UNIXTIME can prevent index usage. </li> <li>the strategy used for multiple values columns can always be applied to all columns, do the minimal query to get your paginated results identifiers (only identifiers in columns, add joins requested for conditions or order by, add subselect for multiple values queries having conditions), count global result and apply pagination on that. Then on the 10/25/50/100 results obtained load all cell data based on the row identifier.</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