Row limit based on display column MySQL

[cgtms]

Checklist

• [X] I have searched budibase discussions and github issues to check if my issue already exists

Hosting

• Self
  • Method: docker compose
  • Budibase Version: 2.24.2

Describe the bug While creating an app using MySQL database with many relationships, querying budibase backend seems to be broken. Behaviour of returned request depends on... 'displayed column'. I have 5 rows in clients table, when maximum 4 is returned. In following screenshots I chose as display column: 'name_surname' (dynamic column: name + surname), only name, job_title. Based on 'display column' it returned 4, 2 or 3 rows - never all 5. I also attach mysql schema.

Log from app: INFO timestamp=2024-05-14T13:21:47.596Z service=@budibase/server req={"method":"POST","url":"/api/datasource_plus_fcfa413934aa4e67b0f8ce46387c0272__employees/search","correlationId":"4e33fd7d-3ba9-4dd3-9caf-e82153e0893d"} res={"status":200} responseTime=5443 msg=request completed

MySQL schema.zip

[linear[bot]]

BUDI-8253 Row limit based on display column MySQL

[mike12345567]

Hi @cgtms - its possible you are hitting the SQL_MAX_ROWS limit for a query - by default this is 5000 rows. You can increase this by setting the environment variable on your app service to something greater than 5000, however the larger this number is the worse your queries will perform, if your data is very heavily related queries may be a better way to go.

https://docs.budibase.com/docs/hosting-settings https://docs.budibase.com/docs/sql-datasource#add-query

[cgtms]

@mike12345567 You are right! That was SQL_MAX_ROWS issue. I increased SQL_MAX_ROWSto 50000 and now I get all records. And also it is performing really slow. I would go your proposal, but If if remove my relations, the whole ease of use and intuitiveness of budibase is gone. I wonder why queries to DB are so over complicated. This example query from this issue:

select a.id as a.id, a.name as a.name, a.surname as a.surname, a.email as a.email, a.phone_number as a.phone_number, a.hour_cost as a.hour_cost, a.monthly_working_hours as a.monthly_working_hours, a.deputy_id as a.deputy_id, a.budi_user as a.budi_user, a.job_title as a.job_title, a.birthday as a.birthday, a.nameday as a.nameday, b.id as b.id, b.name as b.name, b.full_name as b.full_name, b.form_of_business as b.form_of_business, b.state as b.state, b.termination_date as b.termination_date, b.termination_document_url as b.termination_document_url, b.contract_date as b.contract_date, b.contract_document_url as b.contract_document_url, b.tax_office as b.tax_office, b.accounting_records_type as b.accounting_records_type, b.advance_tax_frequency as b.advance_tax_frequency, b.VAT_obligation as b.VAT_obligation, b.VAT_obligation_start_date as b.VAT_obligation_start_date, b.VAT_obligation_advance_tax_frequency as b.VAT_obligation_advance_tax_frequency, b.personnel_employed as b.personnel_employed, b.comment as b.comment, b.NIP as b.NIP, b.KRS as b.KRS, b.PESEL as b.PESEL, b.fk_clients_pricelist as b.fk_clients_pricelist, b.income_tax as b.income_tax, b.VAT_EU_obligation as b.VAT_EU_obligation, b.payroll_work_share as b.payroll_work_share, b.fk_options_zus_clients as b.fk_options_zus_clients, b.declarations as b.declarations, c.id as c.id, c.name as c.name, c.deadline as c.deadline, c.fk_clients_tasks as c.fk_clients_tasks, c.fk_employees_tasks as c.fk_employees_tasks, c.comment as c.comment, c.is_done as c.is_done, c.done_time as c.done_time, c.fk_tasks_scheduled_tasks as c.fk_tasks_scheduled_tasks, c.create_time as c.create_time, c.description as c.description, c.planned_day as c.planned_day, c.planned_month as c.planned_month, c.planned_year as c.planned_year, c.required_for_settlement as c.required_for_settlement, d.id as d.id, d.name as d.name, d.cost as d.cost, d.fk_employees_settlements_extra as d.fk_employees_settlements_extra, d.fk_settlements_extra as d.fk_settlements_extra, d.comment as d.comment, d.datetime_created as d.datetime_created, d.datetime_updated as d.datetime_updated, e.id as e.id, e.fk_settlements_workhours as e.fk_settlements_workhours, e.fk_employees_settlements_workhours as e.fk_employees_settlements_workhours, e.comment as e.comment, e.number_workhours as e.number_workhours, e.datetime_created as e.datetime_created, e.datetime_updated as e.datetime_updated, f.id as f.id, f.fk_employees_reports_months as f.fk_employees_reports_months, f.hour_cost as f.hour_cost, f.working_hours as f.working_hours, f.monthly_cost as f.monthly_cost, f.clients_count as f.clients_count, f.documents_accounting as f.documents_accounting, f.documents_payroll_accounting as f.documents_payroll_accounting, f.documents_auto_accounting as f.documents_auto_accounting, f.revenue as f.revenue, f.income as f.income, f.efficiency as f.efficiency, f.year as f.year, f.month as f.month from (select * from employees as a limit 100) as a left join jt_clients_employees_employee_clients as g on a.id = g.employeesid left join clients as b on b.id = g.clientsid left join tasks as c on a.id = c.fk_employees_tasks left join settlements_extra as d on a.id = d.fk_employees_settlements_extra left join settlements_workhours as e on a.id = e.fk_employees_settlements_workhours left join reports_employees_months as f on a.id = f.fk_employees_reports_months limit 50000

Shouldn't it be limited to only viewed informations instead of every column of each table?

[mike12345567]

@mike12345567 You are right! That was SQL_MAX_ROWS issue. I increased SQL_MAX_ROWSto 50000 and now I get all records. And also it is performing really slow. I would go your proposal, but If if remove my relations, the whole ease of use and intuitiveness of budibase is gone. I wonder why queries to DB are so over complicated. This example query from this issue:

select a.id as a.id, a.name as a.name, a.surname as a.surname, a.email as a.email, a.phone_number as a.phone_number, a.hour_cost as a.hour_cost, a.monthly_working_hours as a.monthly_working_hours, a.deputy_id as a.deputy_id, a.budi_user as a.budi_user, a.job_title as a.job_title, a.birthday as a.birthday, a.nameday as a.nameday, b.id as b.id, b.name as b.name, b.full_name as b.full_name, b.form_of_business as b.form_of_business, b.state as b.state, b.termination_date as b.termination_date, b.termination_document_url as b.termination_document_url, b.contract_date as b.contract_date, b.contract_document_url as b.contract_document_url, b.tax_office as b.tax_office, b.accounting_records_type as b.accounting_records_type, b.advance_tax_frequency as b.advance_tax_frequency, b.VAT_obligation as b.VAT_obligation, b.VAT_obligation_start_date as b.VAT_obligation_start_date, b.VAT_obligation_advance_tax_frequency as b.VAT_obligation_advance_tax_frequency, b.personnel_employed as b.personnel_employed, b.comment as b.comment, b.NIP as b.NIP, b.KRS as b.KRS, b.PESEL as b.PESEL, b.fk_clients_pricelist as b.fk_clients_pricelist, b.income_tax as b.income_tax, b.VAT_EU_obligation as b.VAT_EU_obligation, b.payroll_work_share as b.payroll_work_share, b.fk_options_zus_clients as b.fk_options_zus_clients, b.declarations as b.declarations, c.id as c.id, c.name as c.name, c.deadline as c.deadline, c.fk_clients_tasks as c.fk_clients_tasks, c.fk_employees_tasks as c.fk_employees_tasks, c.comment as c.comment, c.is_done as c.is_done, c.done_time as c.done_time, c.fk_tasks_scheduled_tasks as c.fk_tasks_scheduled_tasks, c.create_time as c.create_time, c.description as c.description, c.planned_day as c.planned_day, c.planned_month as c.planned_month, c.planned_year as c.planned_year, c.required_for_settlement as c.required_for_settlement, d.id as d.id, d.name as d.name, d.cost as d.cost, d.fk_employees_settlements_extra as d.fk_employees_settlements_extra, d.fk_settlements_extra as d.fk_settlements_extra, d.comment as d.comment, d.datetime_created as d.datetime_created, d.datetime_updated as d.datetime_updated, e.id as e.id, e.fk_settlements_workhours as e.fk_settlements_workhours, e.fk_employees_settlements_workhours as e.fk_employees_settlements_workhours, e.comment as e.comment, e.number_workhours as e.number_workhours, e.datetime_created as e.datetime_created, e.datetime_updated as e.datetime_updated, f.id as f.id, f.fk_employees_reports_months as f.fk_employees_reports_months, f.hour_cost as f.hour_cost, f.working_hours as f.working_hours, f.monthly_cost as f.monthly_cost, f.clients_count as f.clients_count, f.documents_accounting as f.documents_accounting, f.documents_payroll_accounting as f.documents_payroll_accounting, f.documents_auto_accounting as f.documents_auto_accounting, f.revenue as f.revenue, f.income as f.income, f.efficiency as f.efficiency, f.year as f.year, f.month as f.month from (select * from employees as a limit 100) as a left join jt_clients_employees_employee_clients as g on a.id = g.employeesid left join clients as b on b.id = g.clientsid left join tasks as c on a.id = c.fk_employees_tasks left join settlements_extra as d on a.id = d.fk_employees_settlements_extra left join settlements_workhours as e on a.id = e.fk_employees_settlements_workhours left join reports_employees_months as f on a.id = f.fk_employees_reports_months limit 50000

Shouldn't it be limited to only viewed informations instead of every column of each table?

Hi @cgtms sadly Budibase doesn't have a great way right now to limit the data that it retrieves as its quite difficult to know what data the user will need to see.

Going to close this issue as the original problem has been solved, sadly to improve the performance of a query like this would require a lot of work, we may look in the future at an approach that allows reducing the amount of information Budibase pulls back (such as removing un-needed relationships) but currently this is simply a limitation of how Budibase works.