• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
softDelete and database indexing

#1
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?
Reply

#2
@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.
Reply

#3
(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.
Test your translation files with Translation Tester
Reply

#4
(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.
Reply

#5
(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
Test your translation files with Translation Tester
Reply

#6
@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/1844...ean-fields
https://stackoverflow.com/questions/1052...lean-field
Reply

#7
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.
Support Development  • Practical CodeIgniter 3  • Vulcan - CLI Tools for CI4
Reply

#8
(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/1844...ean-fields
https://stackoverflow.com/questions/1052...lean-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?
Test your translation files with Translation Tester
Reply

#9
(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!  Cool
Test your translation files with Translation Tester
Reply

#10
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.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.