11–20 of 81 posts
Too bad MySQL does not have partial indexes. We have one huge table I want to add some indexes for specific cases (for max 1% of records) but server will not have enough memory for it if I add those indexes for all records :/
When I did my Oracle DBA training 15 years ago, I learnt about database reorgs. It means basically exporting your database (or tables) and importing it again. What happens is that deleted data which doesn't necessarily free up space (Oracle reuses the freed up space sometimes) doesn't get exported. https://www.iri.com/blog/vldb-operations/database-reorgs-why... https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_Q…
When I did my Oracle DBA training 15 years ago, I learnt about database reorgs. It means basically exporting your database (or tables) and importing it again. What happens is that deleted data which doesn't necessarily free up space (Oracle reuses the freed up space sometimes) doesn't get exported. https://www.iri.com/blog/vldb-operations/database-reorgs-why... https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_Q…
A vacuum full basically does this for a table, copying the data from location A to location B, cleaning up junk. I think index rebuilding may take a separate command?
Too bad MySQL does not have partial indexes. We have one huge table I want to add some indexes for specific cases (for max 1% of records) but server will not have enough memory for it if I add those indexes for all records :/
MySQL has pluggable storage engines. TokuDB does what you're after (adds indexes on the fly, as well as alter tables on the fly without overloading the server).
Too bad MySQL does not have partial indexes. We have one huge table I want to add some indexes for specific cases (for max 1% of records) but server will not have enough memory for it if I add those indexes for all records :/
Could you create a temporary high memory slave MySQL server, sync the master, add the index, sync back to master, and decommission the temporary high memory? I don't know enough about master/slave operations to know if it would work.
Partial indexes can be useful in any case where one value has much higher cardinality than others. Indexing boolean columns is often only useful if one of the values is uncommon and the index is partial to only include those uncommon rows.
Agreed. To explain why this is the case, consider that table in the story that had 99% NULL values. If you were to try to run "SELECT FROM table WHERE column IS NULL", then Postgresql wouldn't use the index anyway, because it would be faster to just read sequentially through the entire table and filter out the 1% that don't match.
Too bad MySQL does not have partial indexes. We have one huge table I want to add some indexes for specific cases (for max 1% of records) but server will not have enough memory for it if I add those indexes for all records :/
MySQL has pluggable storage engines. TokuDB does what you're after (adds indexes on the fly, as well as alter tables on the fly without overloading the server).
> TokuDB has been deprecated by its upstream maintainer. It is disabled from MariaDB 10.5 and has been been removed in MariaDB 10.6 - MDEV-19780. We recommend MyRocks as a long-term migration path.
https://mariadb.com/kb/en/tokudb/
Is MyRocks comparable?
> If for some reason you had to stop the rebuild in the middle, the new index will not be dropped. Instead, it will be left in an invalid state and consume space.
Well, that sure sounds like a bug in PostreSQL to me.
Summary: if you have an index on a column which is mostly NULL, consider using a partial index covering only the records where it's non-NULL.
create index users_email on users(email) where status != 'delete'