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 …
Tag: performance
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 …
Fastest way to determine if record exists
As the title suggests… I’m trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not. Sample query: Say the ? is swapped with ‘TB100’… both the first and second queries will return the exact same result (say… 1 for this c…
Normalize array subscripts so they start with 1
PostgreSQL can work with array subscripts starting anywhere. Consider this example that creates an array with 3 elements with subscripts from 5 to 7: Returns: We get the first element at subscript 5: I want to normalize 1-dimensional arrays to start with array subscript 1. The best I could come up with: The s…
SQL performance MAX()
Just got a small question. When trying to get a single max-Value of a table. Which one is better? or I’m using Microsoft SQL Server 2012 Answer There will be no difference as you can test yourself by inspecting the execution plans. If id is the clustered index, you should see an ordered clustered index …
SQL count(*) performance
I have a SQL table BookChapters with over 20 millions rows. It has a clustered primary key (bookChapterID) and doesn’t have any other keys or indexes. It takes miliseconds to run the following query However, it takes over 10 minutes when I change it like so or Why is that? How can I get select count(*) …
Index for nullable column
I have an index on a nullable column and I want to select all it’s values like this: In the explain plan I see a FULL TABLE SCAN (even a hint didn’t help) Does use the index… I googled and found out there are no null entries in indexes, thus the first query can’t use the index. My ques…
What are the pros and cons of performing calculations in sql vs. in your application
shopkeeper table has following fields: Let’s say, I have the above table. I want to get the records for yesterday and generate a report by having the amount printed to cents. One way of doing is to perform calculations in my java application and execute a simple query and then loop through the records a…
MySQL PRIMARY KEYs: UUID / GUID vs BIGINT (timestamp+random)
tl;dr: Is assigning rows IDs of {unixtimestamp}{randomdigits} (such as 1308022796123456) as a BIGINT a good idea if I don’t want to deal with UUIDs? Just wondering if anyone has some insight into …
MySQL “IN” operator performance on (large?) number of values
I have been experimenting with Redis and MongoDB lately and it would seem that there are often cases where you would store an array of id’s in either MongoDB or Redis. I’ll stick with Redis for this question since I am asking about the MySQL IN operator. I was wondering how performant it is to lis…