indexing – Page 5 – Tarik Billa

How to figure out if mysql index fits entirely in memory

December 16, 2023 by Tarik

Depends on Storage Engine MyISAM (Caches Index Pages From .MYI files) SELECT FLOOR(SUM(index_length)/POWER(1024,2)) IndexSizesMB FROM information_schema.tables WHERE engine=”MyISAM” AND table_schema NOT IN (‘information_schema’,’performance_schema’,’mysql’); Subtract that from key_buffer_size. If the answer > 0, then Yes InnoDB (Caches Data and Index Pages) SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB FROM information_schema.tables WHERE engine=”InnoDB”; Subtract that from innodb_buffer_pool_size. If the answer > … Read more

How to determine what type of index to use in Postgres?

December 16, 2023 by Tarik

Postgres support B-tree, R-tree, Hash, GiST and GIN indexing types. B-tree indexing is the most common and fits most common scenarios. This is the syntax: CREATE INDEX idex_name ON table_name USING btree(column1, column2); Here is the createindex documentation and here is more info on different indextypes in postgres. What type of index you should use … Read more

Indexing over the last axis when you don’t know the rank in advance

December 15, 2023 by Tarik

b = a[…, -k:] This is mentioned in the docs.

How is numpy’s fancy indexing implemented?

December 15, 2023 by Tarik

You have three questions: 1. Which __xx__ method has numpy overridden/defined to handle fancy indexing? The indexing operator [] is overridable using __getitem__, __setitem__, and __delitem__. It can be fun to write a simple subclass that offers some introspection: >>> class VerboseList(list): … def __getitem__(self, key): … print(key) … return super().__getitem__(key) … Let’s make an … Read more

PostgreSQL HASH index

December 14, 2023 by Tarik

Hashes are faster than B-Trees for cases where you have a known key value, especially a known unique value. Hashes should be used if the column in question is never intended to be scanned comparatively with < or > commands. Hashes are O(1) complexity, B-Trees are O(log n) complexity ( iirc ) , ergo, for … Read more

Indexing Null Values in PostgreSQL

December 13, 2023 by Tarik

You could try a partial index: CREATE INDEX idx_partial ON othertable (m_id) WHERE (col1 is not null and col2 is not null and col3 is not null); From the docs: http://www.postgresql.org/docs/current/interactive/indexes-partial.html

Select columns with all zero entries in a pandas dataframe

December 13, 2023 by Tarik

I’d simply compare the values to 0 and use .all(): >>> df = pd.DataFrame(np.random.randint(0, 2, (2, 8))) >>> df 0 1 2 3 4 5 6 7 0 0 0 0 1 0 0 1 0 1 1 1 0 0 0 1 1 1 >>> df == 0 0 1 2 3 4 5 … Read more

Do I need to create separate index for primary key of relational database table

December 12, 2023 by Tarik

You don’t need to. The primary key is already an index.

What are the disadvantages to Indexes in database tables?

December 11, 2023 by Tarik

One index on a table is not a big deal. You automatically have an index on columns (or combinations of columns) that are primary keys or declared as unique. There is some overhead to an index. The index itself occupies space on disk and memory (when used). So, if space or memory are issues then … Read more

Lucene.Net Best Practices

December 6, 2023 by Tarik

If you’re going to work with Lucene, I’d buy a good book that covers it from A to Z. Lucene has a very steep learning curve (in my opinion). It’s not only knowing how to search your that’s important – it’s also about indexing it. Doing a basic search is easy, but creating an index … Read more