+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: softDelete and database indexing (/showthread.php?tid=74198)
Pages:
1
2
softDelete and database indexing - MGatner - 08-21-2019
For the database gurus… CI4's `softDeletes` feature means that the `deleted_at` field will be a part of pretty much every read query for a model’s table. When creating indices, should I be indexing everything against `deleted_at` as well? Both directions?
RE: softDelete and database indexing - php_rocs - 08-21-2019
@MGatner,
There is no need to index that field. It's only purpose is to exclude the deleted records from queries that should only show non deleted records.
RE: softDelete and database indexing - includebeer - 08-21-2019
(08-21-2019, 02:03 PM)php_rocs Wrote: @MGatner,
There is no need to index that field. It's only purpose is to exclude the deleted records from queries that should only show non deleted records.
If it’s used in every request then it could be a good idea to index it. But it I think it will only improve performance if you have a lot of record. For a small table, it won’t make a difference.
RE: softDelete and database indexing - php_rocs - 08-21-2019
(08-21-2019, 04:09 PM)includebeer Wrote:(08-21-2019, 02:03 PM)php_rocs Wrote: @MGatner,
There is no need to index that field. It's only purpose is to exclude the deleted records from queries that should only show non deleted records.
If it’s used in every request then it could be a good idea to index it. But it I think it will only improve performance if you have a lot of record. For a small table, it won’t make a difference.
I disagree because there is no gains in indexing speed (regardless of the database size). This is assuming that the field is boolean. Sometimes you can over index.
RE: softDelete and database indexing - includebeer - 08-22-2019
(08-21-2019, 08:23 PM)php_rocs Wrote: I disagree because there is no gains in indexing speed (regardless of the database size). This is assuming that the field is boolean. Sometimes you can over index.
I'm not sure I understand what you mean by that. An index is used to improve the performance of database access. If you have a lot of rows and you always us the same column in all your where clauses, an index will improve the speed. But it can also slow you down if you have too many indexes. It really depends on the amount of data and the kind of request your application do.
A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
https://www.tutorialspoint.com/mysql/mysql-indexes.htm
RE: softDelete and database indexing - php_rocs - 08-22-2019
@includebeer,
I would go into more details (I use to be a database administrator) but it's not that deep but here are some useful links...
https://stackoverflow.com/questions/1844991/indexing-boolean-fields
https://stackoverflow.com/questions/10524651/is-there-any-performance-gain-in-indexing-a-boolean-field
RE: softDelete and database indexing - kilishan - 08-22-2019
My typical process is just to ensure that any place you join tables is indexed and don't worry about the others unless we start seeing a performance issue.
RE: softDelete and database indexing - includebeer - 08-23-2019
(08-22-2019, 12:38 PM)php_rocs Wrote: @includebeer,
I would go into more details (I use to be a database administrator) but it's not that deep but here are some useful links...
https://stackoverflow.com/questions/1844991/indexing-boolean-fields
https://stackoverflow.com/questions/10524651/is-there-any-performance-gain-in-indexing-a-boolean-field
Thanks for the explanation, I didn't know there were no benefit in indexing a boolean field.
But, the soft delete field in CI4 is not boolean, it's a timestamp. So an index may make sense if there's a lot of data. What do you think?
RE: softDelete and database indexing - includebeer - 08-23-2019
(08-22-2019, 09:31 PM)kilishan Wrote: My typical process is just to ensure that any place you join tables is indexed and don't worry about the others unless we start seeing a performance issue.
Yes, we should avoid premature optimization!
RE: softDelete and database indexing - dave friend - 08-23-2019
Cardinality matters for indexing. A very small number of distinct values among a very large number of rows will not produce noticeable efficiency gains. Indexes are B-tree structures. There is little point in searching against a B-Tree when the size of the tree is small. Use indexes with fields with high cardinality to achieve search performance gains versus sequential scans. With that in mind it might make sense to consider multi-column indexes that include the soft delete column. Perhaps even to the point of including soft deletes into the primary key.