My question is simple, is it quicker(the query operation/processing) to write queries that make use of JOINs to find the string values for ID columns or is it just as fast to use the built-in foreign keys feature in WPDT?
Example, if I have a custom column that has an ID of a user, and when ran I want it to get the first and last name of that user to display it instead of the ID. Would the operation be quicker if I used JOIN queries or would it be just as fast if i created a table in WPDT for the Users table and used that table as the foreign key table using the built in WPDT features?
The above is in regard to generating the output from the DB.
---
Another scenario is searching, if I write the queries to include the JOIN syntax, and a user decides to search that generated column by String (example display name), the display name column is not indexed, so it would result in a slower query.
If I use the built-in WPDT feature for foreign keys, would it then notice that "John Doe" has ID 15 and use that to do the search instead, resulting in a faster query? (ID column is indexed in default wp Users table)
It's always faster to use a query, since all data is loaded through server-side processing, while foreign-key uses JavaScript.
Please note that the PHP SQL parser we use can cause issues with JOIN, UNION, CONCAT and sub-queries. If you can't filter through your server-side tables, the issue is probably with the query you're using. Any query that has JOIN, CONCAT, GROUP, UNION, etc. statements will cause issues with filtering, search and sorting.
To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also, server-side processing feature adds this part of statement automatically when users trigger the sorting on the front-end, and having it in initial statement may cause the table to crash.
My question is simple, is it quicker(the query operation/processing) to write queries that make use of JOINs to find the string values for ID columns or is it just as fast to use the built-in foreign keys feature in WPDT?
Example, if I have a custom column that has an ID of a user, and when ran I want it to get the first and last name of that user to display it instead of the ID. Would the operation be quicker if I used JOIN queries or would it be just as fast if i created a table in WPDT for the Users table and used that table as the foreign key table using the built in WPDT features?
The above is in regard to generating the output from the DB.
---
Another scenario is searching, if I write the queries to include the JOIN syntax, and a user decides to search that generated column by String (example display name), the display name column is not indexed, so it would result in a slower query.
If I use the built-in WPDT feature for foreign keys, would it then notice that "John Doe" has ID 15 and use that to do the search instead, resulting in a faster query? (ID column is indexed in default wp Users table)
Hi TheLoneDev
Thank you for reaching out to us.
It's always faster to use a query, since all data is loaded through server-side processing, while foreign-key uses JavaScript.
Please note that the PHP SQL parser we use can cause issues with JOIN, UNION, CONCAT and sub-queries. If you can't filter through your server-side tables, the issue is probably with the query you're using. Any query that has JOIN, CONCAT, GROUP, UNION, etc. statements will cause issues with filtering, search and sorting.
To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
Please note some this when working with the server-side processing feature:
Ok thank you
You are most welcome
If there is anything else we can assist you with please don't hesitate to open a new ticket.
Have a wonderful day!