Welcome Guest, Not a member yet? Register   Sign In
softDelete and database indexing
#1

(This post was last modified: 08-21-2019, 11:24 AM by MGatner.)

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.
CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/
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
CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/
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.
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?
CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/
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
CodeIgniter 4 tutorials (EN/FR) - https://includebeer.com
/*** NO support in private message - Use the forum! ***/
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




Theme © iAndrew 2016 - Forum software by © MyBB