We're Moving to a New Support Platform – Starting June 1st!

We’re excited to let you know that starting June 1st, we’ll be transitioning to a new support system that will be available directly on our product websites – Amelia, wpDataTables, and Report Builder. In fact, the new support platform is already live for Amelia and wpDataTables, and we encourage you to reach out to us there.

You'll always be able to reach us through a widget in the bottom right corner of each website, where you can ask questions, report issues, or simply get assistance.

While we still do not offer live support, a new advanced, AI-powered assistant, trained on our documentation, use cases, and real conversations with our team, is there to help with basic to intermediate questions in no time.

We're doing our best to make this transition smooth and hassle-free. After June 1st, this current support website will redirect you to the new "Contact Us" pages on our product sites.

Thanks for your continued support and trust – we’re excited to bring you an even better support experience!

Okay
  Public Ticket #2742533
Speed of built in foreign tables feature vs MySQL join queries
Closed

Comments

  •  1
    Uriahs started the conversation
    April 6, 2021 at 5:02pm

    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)

  • [deleted] replied
    April 7, 2021 at 3:30pm

    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:

    • 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.
  •  1
    Uriahs replied
    April 13, 2021 at 2:39pm

    Ok thank you

  • [deleted] replied
    April 14, 2021 at 6:37am

    You are most welcomesmile.png

    If there is anything else we can assist you with please don't hesitate to open a new ticket.

    Have a wonderful day!