I have the following query: As you can see, the cost of the last index scan on chaindata_tokentransfer_chain_id_block_number_tx_eeeac2a4_idx is very high. And the query is timing out. If I remove the filter on chain_id and block_number from the query, then the query is executing in a reasonable amount of time…
Tag: indexing
Delete operation is slow and rebuilding index doesn’t seems to solve the issue
I have a simple delete query : ImportLog table has around 3 million records with all log details. I have non clustered index created on ImportLog table on ImportLogID. The fragementation is less than 10% but still the query is taking more time to execute but when I rebuild the index manually it is executed wi…
Alter a table which has non-unique non-clustered index to add a column
I have a table named Person which already have few non-unique non-clustered index and one clustered index (based on primary key). I have to write two queries: I need to alter the table to add column birthplace. Do I need to check any index while writing the alter table person add birthplace varchar(128) not n…
can indexing SQL tables improve the performance of a tableau dashboard?
I have a very high level question. could indexes on a sql server table improve the loading performance of a tableau dashboard? if so – is there any best practice / guideline we could follow? thanks a lot Answer Yes, it will speed up Tableau. Just like the indexes speed up any query (if applied properly)…
How to improve Clustered Index scan to Clustered Index seek?
I have two tables with primary clustered keys by id. Also I have stored procedure The Execution plan shows me a ‘Clustered Index Scan’ that I want to avoid. Is there any way I can convert it to a ‘Clustered Index Seek’? Execution Plan screen <db fiddle> Answer As I mentioned in t…
How does a multi-column index work in MySQL?
More specifically, what data structure does MYSQL use for multi-column indexing? I know MYSQL use Btree for indexing, which can only index one column. How does multi-column indexing work then? Answer Think of a MySQL “composite” index this way. Concatenate all the columns in the index together, th…
Multi-column Index on a filter & sort by query
I am executing an query of type: Query: Table: entity also has an index (Btree) on it as such – Index: But it seems that this type of index is of no use on the above query. Can anyone suggest what kind of index would better serve this type of use-case? Some Additional Information – The Table is qu…
Gist index in PostgreSQL only works on order, but not on where predicate
I’ve got a plain table with LatLon column containing a point with object location in space with GiST index created on this column: I’ve got a query on selecting only points within some distance to the point with fixed coordinates: Although explain on such query shows the index is not being used du…
How does foreign key resolve queries in terms of performance(indexing) on databases?
I’m wondering how queries with join operation perform when I have or don’t have a foreign key. For example, given the following schema: And given I have the following query: When I ask for the execution plan of it, it gives me the result below. It means the query is using the index I_EMP_DEPTID to…
Does Adding Indexes speed up String Wildcard % searches?
We are conducting a wildcard search on a database table with column string. Does creating a non-clustered index on columns help with wildcard searches? Will this improve performance? Proposed Index: for this query Currently using Microsoft SQL Server 2019. Answer Creating a normal index will not help(*), but …