Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint ’employee_ibfk_1′ in the referenced table ‘branch’


CREATE TABLE Employee( Emp_ID INT PRIMARY KEY, First_Name VARCHAR(25), Last_Name VARCHAR(25), Birth_Day DATE, Sex VARCHAR(1), Salary INT, Super_ID INT, Branch_ID INT );

CREATE TABLE Branch( Branch_ID INT, Branch_Name VARCHAR(40), Mgr_ID INT, Mgr_Start_Date DATE, FOREIGN KEY(Mgr_ID) REFERENCES Employee(Emp_ID) );


MongoDB index issue

I have a simple collection that contains two fields: timestamp and value (float). I need to get min and max values in the timestamp range. The collection contains approx 10000000 documents. I’ve add indexes like {"value" : 1, "timestamp" : 1} and {"value" : -1, "timestamp" : 1}. Queries like db.getCollection('Sample').find({'timestamp' : {'$ gte': 1714284644, '$ lte': 1745820644}}).sort({'value':-1}).limit(1) took about 1 second. Count without limit shows 525600 documents. "Explain" show that query covered by one of the idexes. IXSCAN takes most of time. Is there any way to speed this query up?

Understanding postgres query planner behaviour on gin index

need your expert opinion on index usage and query planner behaviour.

\d orders                                          Partitioned table "public.orders"            Column            |           Type           | Collation | Nullable |                   Default -----------------------------+--------------------------+-----------+----------+----------------------------------------------  oid                         | character varying        |           | not null |  user_id                     | character varying        |           | not null |  tags                        | text[]                   |           | not null |  category                    | character varying        |           |          |  description                 | character varying        |           |          |  order_timestamp             | timestamp with time zone |           | not null |  ..... Partition key: RANGE (order_timestamp) Indexes:     "orders_uid_country_ot_idx" btree (user_id, country, order_timestamp)     "orders_uid_country_cat_ot_idx" btree (user_id, country, category, order_timestamp desc)     "orders_uid_country_tag_gin_idx" gin (user_id, country, tags) WITH (fastupdate=off)     "orders_uid_oid_ot_key" UNIQUE CONSTRAINT, btree (user_id, oid, order_timestamp) 

I have observed the following behaviour based on query param when I run the following query, select * from orders where user_id = 'u1' and country = 'c1' and tags && '{t1}' and order_timestamp >= '2021-01-01 00:00:00+00' and order_timestamp < '2021-03-25 05:45:47+00' order by order_timestamp desc limit 10 offset 0

case 1: for records with t1 tags where t1 tags occupies 99% of the records for user u1, 1st index orders_uid_country_ot_idx is picked up.

Limit  (cost=0.70..88.97 rows=21 width=712) (actual time=1.967..12.608 rows=21 loops=1)    ->  Index Scan Backward using orders_y2021_jan_to_uid_country_ot_idx on orders_y2021_jan_to_jun orders  (cost=0.70..1232.35 rows=293 width=712) (actual time=1.966..12.604 rows=21 loops=1)          Index Cond: (((user_id)::text = 'u1'::text) AND ((country)::text = 'c1'::text) AND (order_timestamp >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (order_timestamp < '2021-03-25 05:45:47+00'::timestamp with time zone))          Filter: (tags && '{t1}'::text[])  Planning Time: 0.194 ms  Execution Time: 12.628 ms 

case 2: But when I query for tags value t2 with something like tags && '{t2}' and it is present in 0 to <3% of records for a user, gin index is picked up.

Limit  (cost=108.36..108.38 rows=7 width=712) (actual time=37.822..37.824 rows=0 loops=1)    ->  Sort  (cost=108.36..108.38 rows=7 width=712) (actual time=37.820..37.821 rows=0 loops=1)          Sort Key: orders.order_timestamp DESC          Sort Method: quicksort  Memory: 25kB          ->  Bitmap Heap Scan on orders_y2021_jan_to_jun orders  (cost=76.10..108.26 rows=7 width=712) (actual time=37.815..37.816 rows=0 loops=1)                Recheck Cond: (((user_id)::text = 'u1'::text) AND ((country)::text = 'ID'::text) AND (tags && '{t2}'::text[]))                Filter: ((order_timestamp >= '2021-01-01 00:00:00+00'::timestamp with time zone) AND (order_timestamp < '2021-03-25 05:45:47+00'::timestamp with time zone))                ->  Bitmap Index Scan on orders_y2021_jan_to_uid_country_tag_gin_idx  (cost=0.00..76.10 rows=8 width=0) (actual time=37.812..37.812 rows=0 loops=1)                      Index Cond: (((user_id)::text = 'u1'::text) AND ((country)::text = 'c1'::text) AND (tags && '{t2}'::text[]))  Planning Time: 0.190 ms  Execution Time: 37.935 ms 
  1. Is this because the query planner identifies that since 99% of the records is covered in case 1, it skips the gin index and directly uses the 1st index? If so, does postgres identifies it based on the stats?

  2. Before gin index creation, when 1st index is picked for case 2, performance was very bad since index access range is high. i.e number of records that satisfies the condition of user id, country and time column is very high. gin index improved it but i’m curious to understand how postgres chooses it selectively.

  3. orders_uid_country_cat_ot_idx was added to support filter by category since when gin index was used when filtered by just category or by both category and tags, the performance was bad compared to when the btree index of user_id, country, category, order_timestamp is picked up . I expected gin index to work well for all the combination of category and tags filter. What could be the reason? The table contains millions of rows

Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

We have an application generated query using a view that has two tables joined on a LEFT OUTER join. When filtering by fields from just one table (either table) an index seek happens and it’s reasonably fast. When the where clause includes conditions for fields from both tables using an OR the query plan switches to a table scan and doesn’t utilize any of the indexes.

All four fields that are being filtered on are indexed on their respective tables.

Fast query plan where I filter on 3 fields from one table:

Slow query plan where I filter on four fields…three from one table and one from another table:

Ideally I would like to understand why this is happening and how to nudge the query engine to utilize all the indexes.

body mass index (BMI) calculator with cool images

I'm selling my website
body mass index or BMI
This website will help your visitors calculate their BMI (Body Mass Index)

Why this niche is important?
Weight has been one of the things most people have become focused on in later years
The body mass index or BMI as it’s usually shortened is a simple method, devised in order to determine, based on the height and current weight of a person whether or not they need to start…

body mass index (BMI) calculator with cool images

How to get my specific Item index on inventory when pickup?

So I want to make some inventory system for my game, and I found some issue when collecting the item, what I want here matching the index Inventory_UI system exactly the same as currentIndex on the collected item, then display it on Inventory_UI system, let say I pickUp some item with the currentIndex = 1 so on Inventory_UI system index=1 too and so on. this happening when inventory description to so the id is same as itemIndex picked up.

so here the the script:

  • ItemManager( this for information of item ):
[System.Serializable] public class theItems {     public string Name;     public GameObject[] allCurrentPrefabs;     public int numberOfIndex;     public AudioClip[] allSound;     public string[] terjemahannya;     public string[] latinnya; }  public class _ItemManager : MonoBehaviour {     public static _ItemManager instance;      private void Awake()     {         instance = this;     }      public List<theItems> items;      [Header("SurahApa")]     public int Surah;      [Header("SoundStuff")]     public AudioSource aSource;       // Start is called before the first frame update     void Start()     {      }      // Update is called once per frame     void Update()     {      }      public void PlaySurah(int surahnya, int ayatnya)     {         aSource.clip = items[surahnya].allSurahSound[ayatnya];         aSource.Play();     } } 
  • CollectibleItem ( this attach to each item )
 public static _CollectibleAyat instance;      private void Awake()     {         instance = this;     }      public enum InteractionType     {         none, pickUp, Examine     }      public InteractionType interactType;      public int currentIndex;      public string terjemahannya;     public string latinnya;     public GameObject allCurrentPrefabs;      public UnityEvent customevent;      public void Reset()     {         GetComponent<Collider2D>().isTrigger = true;     }      public void interact()     {         switch (interactType)         {             //case InteractionType.none:             //    Debug.Log("None");             //    break;             case InteractionType.Examine:                  _InteractSystem.instance.Examine(this);                  Debug.Log("Examine");                  break;             case InteractionType.pickUp:                 // Add the object to the pickUpItems List                 // Delete the Object                  _InventorySystem.instance.PickUpItem(gameObject);                 gameObject.SetActive(false);                  Debug.Log("PickingUp");                 break;             default:                  Debug.Log("Null Item");                 break;         }          //Invoke (Call) the custom Event(s)         customevent.Invoke();     } 
  • This InventorySystem( when item pickup)
public static _InventorySystem instance;      private void Awake()     {         instance = this;     }      [Header("GeneralFields")]     public List<GameObject> items = new List<GameObject>();     public bool isOpen;     public int ayah;      [Header("UI Items Section")]     public GameObject UI_InventoryWindow;     public Image[] item_Images;      // Start is called before the first frame update     void Start()     {      }      // Update is called once per frame     void Update()     {         if (Input.GetKeyDown(KeyCode.I))         {             ToggleInventory();         }     }      public void ToggleInventory()     {         isOpen = !isOpen;         UI_InventoryWindow.SetActive(isOpen);     }      public void PickUpItem(GameObject item)     {         items.Add(item);         Update_UI();     }      public void Update_UI()     {         HideAll();          for (int i = 0; i < items.Count; i++)         {             item_Images[i].sprite = items[i].GetComponent<SpriteRenderer>().sprite;             item_Images[i].gameObject.SetActive(true);          }      }      public void HideAll()     {         foreach (var i in item_Images)         {             i.gameObject.SetActive(false);         }     }      //SampeSini     public void ShowDescription(int id)     {       }      public void HideDescription()     {      } 

note: on the ShowDescription(int id) i want to show item information what same as i picked up i have mentioned it above.

Why doesn’t SQL Server use my index in this SELECT … WHERE?

I’ve created a table with a nonclustered PK (this is by design), and an additional nonclustered index on the column I’m filtering with a WHERE clause ([target_user_id]):

CREATE TABLE [dbo].[MP_Notification_Audit] (     [id]                    BIGINT             IDENTITY (1, 1) NOT NULL,     [type]                  INT                NOT NULL,     [source_user_id]        BIGINT             NOT NULL,     [target_user_id]        BIGINT             NOT NULL,     [discussion_id]         BIGINT             NULL,     [discussion_comment_id] BIGINT             NULL,     [discussion_media_id]   BIGINT             NULL,     [patient_id]            BIGINT             NULL,     [task_id]               BIGINT             NULL,     [date_created]          DATETIMEOFFSET (7) CONSTRAINT [DF_MP_Notification_Audit_date_created] DEFAULT (sysdatetimeoffset()) NOT NULL,     [clicked]               BIT                NULL,     [date_clicked]          DATETIMEOFFSET (7) NULL,     [title]                 NVARCHAR (MAX)     NULL,     [body]                  NVARCHAR (MAX)     NULL,     CONSTRAINT [PK_MP_Notification_Audit1] PRIMARY KEY NONCLUSTERED ([id] ASC) );  [...]  CREATE NONCLUSTERED INDEX [IX_MP_Notification_Audit_TargetUser] ON [dbo].[MP_Notification_Audit] (     [target_user_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO 

This table has about 11,700 rows of data in, so it should be enough to trigger the use of indexes with WHERE clauses. If I SELECT just the column I’m filtering on, only the index is used and 133 matching rows are read – an index-only scan:

SELECT [target_user_id]   FROM [TestDb].[dbo].[MP_Notification_Audit]   WHERE [target_user_id] = 100017 

Execution plan 1

However, as soon as I add an extra column to the SELECT, the index is ignored and a table scan with a predicate is done to attain the result, reading over 11,700 rows:

SELECT [target_user_id], [patient_id]   FROM [TestDb].[dbo].[MP_Notification_Audit]   WHERE [target_user_id] = 100017 

Execution plan 2

Why is it ignoring my index in this second query? I’d have thought it would still be more efficient to use the index to get down to 133 RIDs, then query the extra row data required, than to go through every row of the table with a predicate? I know I can add columns to the index with INCLUDE with the extra fields needed in the SELECT clause to make it use the index again, but I’m interested as to why it doesn’t still use the index in this case.

Super Index list of all DnD published rules? [closed]

has anyone produced a combined index, taking all the separate indexes of all DnD published books and combining it into one resource. For instance today I was looking for trap info, it is present in the DMG, Xanthers Guide etc so I have to go to each book tthat has info and physically check the book index to check for the page I am looking for. I physically have now 10 books so it would be good to be able to have one combined index I can refer to when looking things up, especially if something comes up at the table mid game.

Do I need to create index for the column when using it in update statement?

I have a table in postgresql11 which has a userId as a column. And it is not primary key in the table. I need to perform below update statement on the table.

update entity set name='xxxx' where userId = 'yyy.

The userId is in the where condition but I don’t know whether I should create an index for userId. Will it improve performance? If yes, how can I specify index in the update statement?

How to index Web 2.0(Tier1) from RankerX

Hi friend,

I’ve problem with create backlink for my Web 2.0 (Tier1 from RankerX). It’s don’t have any backlink from Tier1 to my money site after 2 weeks.
Can you guy recommend me some technic to index backlink?

1) I’ve create Web 2.0(Tier1) point to my money site. & use speedlink.
2) I’ve export Tier1 pass to GSA and use general blog comment.

What should I need to do?
I’m sorry for my English… 🙂