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. Since this new less constrained query is working, I’d expect
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 within a second. After a
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 null? My next requirement is I
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), Tableau is the same
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 the comments, the CLUSTERED INDEX on dbo.Employees isn’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, then build an BTree index on that ‘single’ string. Some related comments: Once
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 quite huge,
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 during execution: At the same time, executing a
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 improve the join operation. However,
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 a full-text index will, though you would have to change your query