I have a case where due to speed issues I only want to create the index on records for last 90 days. When I try to create index like this: create index if not exists …
Tag: indexing
how the sql works by in a sql about using index
how can this sql use index and how can this sql not use index. CREATE TABLE `testtable` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT …
How to build an index that is best for this SQL?
I’m querying database by a simple SQL like: SELECT DISTINCT label FROM Document WHERE folderId=123 I need a best index for it. There are two way to implement this. (1) create two indexes CREATE …
Why query is still so fast when I operate a non-indexing column?
I am learning indexing of database. here are indexings of a table. And this table has 330k records. mysql> show index from employee; +———-+————+————-+————–+———…
Optimize MySQL InnoDB query for max, count
I have an MySQL InnoDB table with 5.7M rows and 1.9GB size: +——————-+———+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra …
Two indexes for same column and change the order
I have a large table in Microsoft SQL Server 2008. It has two indexes. One index having column A descending order and another index having the column A ascending with some other columns. My …
Postgres compound index effectiveness with a given column list
Let’s say I have a compound index involving these 3 columns. (name, email, phone) Is this index still will be used for these queries? The first query involves just 2 of indexed fields and the second …
case statement in index creation
How do i use a CASE statement when creating a UNIQUE INDEX? My statement looks like this CREATE UNIQUE INDEX my_unique_creation ON junk ((CASE WHEN nlevel(path) > 1 THEN (…
VIEW – INDEX SQL SERVEr
Does anyone know another way to run the query below more efficiently? I’m using SQL Server 2014. I’m facing a problem if i create index on table or on view . On table 2 (TB_FATURA_ITEM_TRANSACAO_HST) , has stored 50 million rows . I was wondering using Schemabiding on View , but I was looking in some articles that is not
PostgreSQL Partial Indexes and UPSERT
After googling a lot my question is described below: Attempting to insert values: Results in: Altho this works(per spec): PostgreSQL documentation stands that it should work PostgreSQL v9.5 My aim is to find way to create unique index on this table on multiple nullable columns and update old rows with new ones on UPSERT Answer The conflict_target used in on