SQL tips
Optimizing your sql query
Here we have some tips for queries. Because all the time we need to optimize our code, our queries, our methods.
Wildcard
In SQL, wildcard is provided for us with '%' symbol. Using wildcard will definitely slow down your query especially for table that are really huge. We can optimize our query with wildcard by doing a postfix wildcard instead of pre or full wildcard.#Full wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';
#Postfix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE 'hello%';
#Prefix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello';
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';
#Postfix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE 'hello%';
#Prefix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello';
That column must be indexed for such optimize to be applied.
P.S: Doing a full wildcard in a few million records table is equivalence to killing the database.
NOT operator
Try to avoid NOT operator in SQL. It is much faster to search for an exact match (positive operator) such as using the LIKE, IN, EXIST or = symbol operator instead of a negative operator such as NOT LIKE, NOT IN, NOT EXIST or != symbol. Using a negative operator will cause the search to find every single row to identify that they are ALL not belong or exist within the table. On the other hand, using a positive operator just stop immediately once the result has been found. Imagine you have 1 million record in a table.COUNT vs EXIST
Some of us might use COUNT operator to determine whether a particular data existSELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0;
Similarly, this is very bad query since count will search for all record exist on the table to determine the numeric value of field 'COLUMN'. The better alternative will be to use the EXIST operator where it will stop once it found the first record.
Index Unique Column
Some database such as Mysql search better with column that are unique and indexed. It is best to remember to index those columns that are unique. And if the column is truly unique, declare them as one. However, if that particular column was never used for searching purposes, it gives no reason to index that particular column although it is given unique.Max and Min Operators
Max and Min operators look for the maximum or minimum value in a column. We can use Max or Min on columns that already established such Indexes. But if that particular column is frequently use, having an index should help speed up such searching and at the same time speed max and min operators. This makes searching for maximum or minimum value faster. Deliberate having an index just to speed up Max and Min is always not advisable.Deocamdata sa tin minte de un am luat ponturile: https://www.hungred.com/useful-information/ways-optimize-sql-queries/
