Explanation I have a table which does not have a primary key (or not even a composite key). The table is for storing the time slots (opening hours and food delivery available hours) of the food shops. Let’s call the table “business_hours” and the main fields are as below. shop_id day (0 R…
Tag: indexing
SQL Server indexing includes questions
I’ve been trouble shooting some bad SQL calls in my works applications. I’ve been reading up on indexes, tweaking and benchmarking things. Here’s some of the rules I’ve gathered (let me know if this …
What’s the proper index for querying structures in arrays in Postgres jsonb?
I’m experimenting with keeping values like the following in a Postgres jsonb field in Postgres 9.4: I’m executing queries like: How would I create an index on that data for queries like the above to utilize? Does this sound reasonable design for a few million rows that each contain ~10 events in t…
SQL: Insert into table and return new index?
I have a database with auto-increment index. When I inserting a new item in the table, I don’t know the index number. Is there a way to return new index of my item simultaneously? I could assume that …
SQL Server range indexing ideas
I need help understanding how to create proper indexing on a table for fast range selects. I have a table with the following columns: Column — Type frameidx — int u — int v — int x — float(53) y — float(53) z — float(53) None of these columns is unique. There are to b…
Does Order of Fields of Multi-Column Index in MySQL Matter
I know the importance of indexes and how order of joins can change performance. I’ve done a bunch of reading related to multi-column indexes and haven’t found the answer to my question. I’m curious if I do a multi-column index, if the order that they are specified matters at all. My guess is…
Dynamics AX strange index functionality
At work we have a customer which doesn’t allow synchronizing. They have a database trigger which stops all Dynamics’ synchronization stuff. Now we are moving on to a plan where the synchronisation is allowed but I have to make a list of all indexes that do not match between AX and the SQL database…
Link To Entities, how to fix Startswith resolving to sql CHARINDEX and ignoring index
I have the following model I’m calling this function The above is trying to reproduce the following sql However it’s generating the following nasty piece of work: As you can see, it’s generating CHARINDEX for the StartsWith. The problem is this is ignoring all the indexing I have setup, and …
Optimize performance for queries on recent rows of a large table
I have a large table: CREATE TABLE “orders” ( “id” serial NOT NULL, “person_id” int4, “created” int4, CONSTRAINT “orders_pkey” PRIMARY KEY (“id”) ); 90% of all requests are about orders from the …
Oracle: Single multicolumn index or two single column indexes
I have table create table1( column1 number(10, column2 number(10), column3 number(10) ); column1 is Primary Key column2 and column3 is Foreign key I have created unique constraint on 2 …