Skip to content

Instantly share code, notes, and snippets.

@derrickmehaffy

derrickmehaffy/raw SQL queries

Created March 30, 2023 21:57
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save derrickmehaffy/2839223ea2088dda429c60472d95a116 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save derrickmehaffy/2839223ea2088dda429c60472d95a116 to your computer and use it in GitHub Desktop.
Download ZIP
Performance of updating components
Raw
gistfile1.txt
Breakdown of each query performed with a just single component update (repeat component) changing one text field
> http: PUT /content-manager/collection-types/api::test.test/2 (224 ms) 200
- Find admin user who performed the request based on ID
- Find role of admin user
- Get permissions from that role
- Get parent "test" content-type data
- Create join for component data from "test" content-type
- Again get admin user who performed request (why?)
- 3rd time fetching admin user (why?)
- Unknown "first" SQL request to fetch the test content-type data again
- Get a count of the admin users for this entry (why?)
- Again fetch the test content-type
- Component order select
- Again fetching component data
- 3rd time fetching component data
- 4th time fetching component data
- Finally doing the first update to component data (id 1)
- updating other unrelated component (id 2)
- updating other unrelated component (id 3)
- refetching component data
- 2nd refecting of component data
- 3rd refetching of component data
- refetching test content-type data
- updating parent content-type data
- selecting component data again
- delete all component data
- recreate component data
- select test content-type data again
- select component data again
- select admin user data again
- another select admin user data
Total queries for one field update in a single component entry part of a repeatable component
31
Raw
raw SQL queries
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1, 1 ],
__knexQueryUid: 'mzfMy4pjkQroDgxIS0n7y',
sql: 'select `t0`.*, `t0`.`id` from `admin_users` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1 ],
__knexQueryUid: 'zQNs2cfSko9SNc9T7fWAG',
sql: 'select distinct `t1`.`role_order`, `t0`.*, `t1`.`user_id` from `admin_roles` as `t0` left join `admin_users_roles_links` as `t1` on `t0`.`id` = `t1`.`role_id` where (`t1`.`user_id` in (?)) order by `t1`.`role_order` asc'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1 ],
__knexQueryUid: 'b4G4tbHfsY5NHDe7tb6fy',
sql: 'select distinct `t0`.* from `admin_permissions` as `t0` left join `admin_permissions_role_links` as `t1` on `t0`.`id` = `t1`.`permission_id` left join `admin_roles` as `t2` on `t1`.`role_id` = `t2`.`id` left join `admin_users_roles_links` as `t3` on `t2`.`id` = `t3`.`role_id` left join `admin_users` as `t4` on `t3`.`user_id` = `t4`.`id` where (`t4`.`id` = ?)'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ '2', 1 ],
__knexQueryUid: 't-hj1yO2MXjADFdZ_WFcK',
sql: 'select `t0`.*, `t0`.`id`, `t0`.`created_by_id`, `t0`.`updated_by_id` from `tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 'test', 2 ],
__knexQueryUid: 'e0t2Qb3AAx76TpvZhZQl_',
sql: 'select distinct `t1`.`order`, `t0`.*, `t1`.`entity_id` from `components_test_tests` as `t0` left join `tests_components` as `t1` on `t0`.`id` = `t1`.`component_id` and `t1`.`field` = ? where (`t1`.`entity_id` in (?)) order by `t1`.`order` asc'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1 ],
__knexQueryUid: 'qHXssaIOP-Yry3TXw8Pnm',
sql: 'select `t0`.*, `t0`.`id` from `admin_users` as `t0` where (`t0`.`id` in (?))'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1 ],
__knexQueryUid: 'LwNakvQhNokBY_lShNabg',
sql: 'select `t0`.*, `t0`.`id` from `admin_users` as `t0` where (`t0`.`id` in (?))'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1 ],
__knexQueryUid: 'aDOcCx74gSFkguguySQfr',
sql: 'select distinct `t0`.* from `admin_roles` as `t0` left join `admin_users_roles_links` as `t1` on `t0`.`id` = `t1`.`role_id` left join `admin_users` as `t2` on `t1`.`user_id` = `t2`.`id` where (`t2`.`id` = ?)'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 2, 1 ],
__knexQueryUid: 'QMeqvj8OuuvH90VUNDbw-',
sql: 'select `t0`.* from `tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1, 1 ],
__knexQueryUid: 'vqtyXfdtV3ooin9Ktsa20',
sql: 'select count(`t0`.`id`) as `count` from `admin_users` as `t0` where (`t0`.`id` in (?)) limit ?'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 2, 1 ],
__knexQueryUid: 'JNAlLXn7re3raGzv1Q6WN',
sql: 'select `t0`.`id` from `tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 'test', 2 ],
__knexQueryUid: 'm_cc8ftcJh-FkKu24k8q2',
sql: 'select distinct `t1`.`order`, `t0`.*, `t1`.`entity_id` from `components_test_tests` as `t0` left join `tests_components` as `t1` on `t0`.`id` = `t1`.`component_id` and `t1`.`field` = ? where (`t1`.`entity_id` in (?)) order by `t1`.`order` asc'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1, 1 ],
__knexQueryUid: 'aiG7NN8SCJEm0P96pqpqc',
sql: 'select `t0`.* from `components_test_tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 2, 1 ],
__knexQueryUid: 'PamQ3lpXywZR4NJqxm3Fb',
sql: 'select `t0`.* from `components_test_tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 3, 1 ],
__knexQueryUid: '2_-20AbqpbUndD28L0QOf',
sql: 'select `t0`.* from `components_test_tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'update',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1, 'testalt2', 1 ],
__knexQueryUid: 'tkTqa7sRnBxi9cvaP01sg',
sql: 'update `components_test_tests` set `id` = ?, `string` = ? where (`id` = ?)',
returning: undefined
}
{
method: 'update',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 2, 'test2', 2 ],
__knexQueryUid: 'CMyD6WFXZCDSW_W-01Su5',
sql: 'update `components_test_tests` set `id` = ?, `string` = ? where (`id` = ?)',
returning: undefined
}
{
method: 'update',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 3, 'test3', 3 ],
__knexQueryUid: 'u1vUJV9xrOb2v9H9N9ZLO',
sql: 'update `components_test_tests` set `id` = ?, `string` = ? where (`id` = ?)',
returning: undefined
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1, 1 ],
__knexQueryUid: 'KJWo1Dew7AhzP8YC7yykf',
sql: 'select `t0`.* from `components_test_tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 2, 1 ],
__knexQueryUid: 'a5GNxwVH0F74GG3VykHa9',
sql: 'select `t0`.* from `components_test_tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 3, 1 ],
__knexQueryUid: 'G3FVq9biw9sbdKQGtvTDg',
sql: 'select `t0`.* from `components_test_tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 2, 1 ],
__knexQueryUid: 'fkNhKMGjItXyEIGCTmWgv',
sql: 'select `t0`.* from `tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'update',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 'test', 1, false, 2023-03-30T21:43:32.904Z, 2 ],
__knexQueryUid: 'qDpDhuBrOY-VZzyNvXoZS',
sql: 'update `tests` set `string` = ?, `updated_by_id` = ?, `prevent_delete` = ?, `updated_at` = ? where (`id` = ?)',
returning: undefined
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [
2, 0,
1, 2,
3, 'test',
100
],
__knexQueryUid: 'qfhuTV_91Dqt7uRaHxa5g',
sql: 'select `t0`.`component_id` from `tests_components` as `t0` where (`t0`.`entity_id` = ? and `t0`.`id` > ? and `t0`.`component_id` not in (?, ?, ?) and `t0`.`field` = ?) order by `t0`.`id` asc limit ?'
}
{
method: 'del',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 2, 0, 'test' ],
__knexQueryUid: 'yoTE3E_aj5RWKKx2uJUdq',
sql: 'delete from `tests_components` where (`entity_id` = ? and 1 = ? and `field` = ?)'
}
{
method: 'raw',
sql: 'UPDATE `tests_components` as a\n' +
' SET `order` = b.src_order\n' +
' FROM (\n' +
' SELECT `id`, ROW_NUMBER() OVER (PARTITION BY `entity_id` ORDER BY `order`) AS src_order\n' +
' FROM `tests_components`\n' +
' WHERE `entity_id` = ?\n' +
' ) AS b\n' +
' WHERE b.id = a.id',
bindings: [ 2 ],
options: {},
__knexQueryUid: 'AidbMGA79cSs9N5Om39bu'
}
{
method: 'insert',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [
1, 'test.test',
2, 'test',
1, 2,
'test.test', 2,
'test', 2,
3, 'test.test',
2, 'test',
3
],
__knexQueryUid: '1A0FY9KDD_gb-7ENOt4k7',
sql: 'insert into `tests_components` (`component_id`, `component_type`, `entity_id`, `field`, `order`) select ? as `component_id`, ? as `component_type`, ? as `entity_id`, ? as `field`, ? as `order` union all select ? as `component_id`, ? as `component_type`, ? as `entity_id`, ? as `field`, ? as `order` union all select ? as `component_id`, ? as `component_type`, ? as `entity_id`, ? as `field`, ? as `order` where true on conflict (`entity_id`, `component_id`, `field`, `component_type`) do update set `order` = excluded.`order` returning `id`',
returning: 'id'
}
{
method: 'first',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 2, 1 ],
__knexQueryUid: 'vRfHf89AHdJ8W-_KKdvsC',
sql: 'select `t0`.*, `t0`.`id`, `t0`.`created_by_id`, `t0`.`updated_by_id` from `tests` as `t0` where (`t0`.`id` = ?) limit ?'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 'test', 2 ],
__knexQueryUid: 'CYEf95TTOM9zXaEM1hYTS',
sql: 'select distinct `t1`.`order`, `t0`.*, `t1`.`entity_id` from `components_test_tests` as `t0` left join `tests_components` as `t1` on `t0`.`id` = `t1`.`component_id` and `t1`.`field` = ? where (`t1`.`entity_id` in (?)) order by `t1`.`order` asc'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1 ],
__knexQueryUid: 'e29-ipVWcZGRWq07w6UqD',
sql: 'select `t0`.*, `t0`.`id` from `admin_users` as `t0` where (`t0`.`id` in (?))'
}
{
method: 'select',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1 ],
__knexQueryUid: 'YqGSv0assqVp2HsnbXRaR',
sql: 'select `t0`.*, `t0`.`id` from `admin_users` as `t0` where (`t0`.`id` in (?))'
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment