The following two indexes are redundant in most SQL databases: CREATE INDEX i_actor_1 ON actor (last_name); CREATE INDEX i_actor_2 ON actor (last_name, first_name); It is usually safe to drop the first index, because all queries that query the LAST_NAME column only can still profit from the second index I_ACTOR_2. The reason being that LAST_NAME is … Continue reading How to Find Redundant Indexes in SQL
Tag: Indexing
Why You Should (Sometimes) Avoid Expressions in SQL Predicates
I've recently discovered a rather significant performance issue on a productive Oracle 11g customer database. And I'm sure you have this issue too, which is why I'm documenting it here. This is a simplified representation of the setup at the customer site: ID PAYMENT_DATE TEXT ---------- ------------ ----------------------------------- 33803 21.05.16 DcTNBOrkQIgMtbietUWOsSFNMIqGLlDw... 29505 09.03.16 VIuPaOAQqzCMlFBYPQtvqUSbWYPDndJD... 10738 … Continue reading Why You Should (Sometimes) Avoid Expressions in SQL Predicates
Does Your Database Really Use Your Index?
Adding the right index to speed up your queries is essential. But after a while, as your system grows, you may find yourself with tons of indexes, which all slow down writing to the database - as with each write to the table, the index needs to be updated as well in the same transaction. … Continue reading Does Your Database Really Use Your Index?
How to Know if a Given Index Can be Dropped
It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove. - Antoine de Saint Exupéry in Terre des Hommes As SQL developers, we keep adding more and more indexes to our tables. Every time we run new queries that are potentially slow, a … Continue reading How to Know if a Given Index Can be Dropped
Do Not Think That One Second is Fast for Query Execution
I keep encountering situations where RDBMS users think that one second for query execution is anything near fast. Most recently, in this Stack Overflow question: Hibernate SQL In clause making CPU usage to 100% The poster's original question was why a similar query executes in one second when executed in SQL Server Management Studio whereas … Continue reading Do Not Think That One Second is Fast for Query Execution
Have You Ever Wondered About the Difference Between NOT NULL and DEFAULT?
When writing DDL in SQL, you can specify a couple of constraints on columns, like NOT NULL or DEFAULT constraints. Some people might wonder, if the two constraints are actually redundant, i.e. is it still necessary to specify a NOT NULL constraint, if there is already a DEFAULT clause? The answer is: Yes! Yes, you … Continue reading Have You Ever Wondered About the Difference Between NOT NULL and DEFAULT?
MongoDB “Lightning Fast Aggregation” Challenged with Oracle
What does "Scale" even mean in the context of databases? When talking about scaling, people have jumped to the vendor-induced conclusion that: SQL doesn't scaleNoSQL scales It is very obvious that NoSQL vendors make such claims. It has also been interesting that many NoSQL consumers made such claims, even if they probably confused SQL in … Continue reading MongoDB “Lightning Fast Aggregation” Challenged with Oracle