Archive for the SQL Category

Count Duplicate Records

Posted in SQL with tags , , , on March 25, 2009 by myleskadusale

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

I got this from SQL Authority Blog

GROUP WISE MAX

Posted in SQL with tags , , , , , on February 19, 2009 by myleskadusale

Today I found nice sites that has good sample how to get the maximum
record in a group check it out.

~jk groupwise max

How to find max row per group without subqueries

🙂

Simple SQL & Database Performance Tips

Posted in SQL with tags , , , , , , , on February 17, 2009 by myleskadusale

SQL Tips
1. Use EXPLAIN or EXPLAIN EXTENDED to know the performance of your query

2. Don’t use DISTINCT when you have or could use GROUP BY

3. Try avoiding the use of LIKE if possible and if you will use LIKE in your query avoid wildcards at the start of LIKE queries

4. The use of LIMIT may not be as fast as it sounds

5. Know when to split a complex query and join smaller ones

6. Make similar queries consistent so cache is used

7. Don’t use COUNT * on Innodb tables for every search, do it a few times and/or summary tables, or if you really need it

8. Use UNION if your sure there are duplicates, if there are no duplicates then use UNION ALL because UNION by default performs the equivalent of a SELECT DISTINCT on the final result set

9. Try to avoid updating heavily indexed columns

10. Try to minimize the updating of tables that have UPDATE triggers

11. Try to avoid using Triggers if possible, incorporate the logic of trigger in stored procedure

12. Those operators at the top will produce results faster than those listed at the bottom.

  1. =
  2. >, >=, <, <=
  3. LIKE
  4. <>

13. When you have a choice of using the IN or the BETWEEN clauses choose BETWEEN clause, as it is much more efficient.

14. In your WHERE clause, put the least likely true AND expression first. This way, if the AND expression is false, the clause will end immediately, saving time.

15. In your WHERE clause, if both parts of an AND expression are equally likely being false, put the least complex AND expression first. This way, if it is false, less work will have to be done to evaluate the expression.

16. Keep the number of rows returned by the query as small as possible

17. Keep the number of groupings as few as possible

18. Don’t group redundant columns

19. Rewriting a query with OR conditions as a UNION

20. Use the WHERE clause instead of using HAVING clause, whenever possible

21. Use table variables instead of temporary tables

22. Try to use constraints instead of triggers, whenever possible.

23. Try to avoid using SQL Server cursors, whenever possible

24. Use views and stored procedures instead of heavy-duty queries

25. Try to restrict the queries result set by returning only the particular columns from the table, not all table’s columns

26.  Put ORDER BY NULL after GROUP BY for better performance, when you do not need GROUP BY results sorted

DATABASE Tips
1. Use indexes on strategic fields, not all fields.

2. Use Triggers wisely

3. Normalize Tables

This my collection of tips that I got from the net

LINKS:
Easy MySQL Performance Tweaks

SQL Joins Simple Notes

Posted in SQL with tags , , , , , on February 17, 2009 by myleskadusale

INNER JOIN
Inner join only returns a result that will contain matching records from both tables.

OUTER JOIN
An outer join is the inverse of the inner join. It only returns those records not in both tables.

LEFT JOIN
A left join returns all the records in the “left” table whether they have a match in the right table or not.

If, however, they do have a match in the right table – give me the “matching” data from the right table as well. If not – fill in the holes with null.

RIGHT JOIN
A right join returns all the records in the “right” table whether they have a match in the left table or not.

If, however, they do have a match in the left table – give me the “matching” data from the left table as well. If not – fill in the holes with null.

FULL JOIN
Result will contain all records from both tables, and fill in NULLs for missing matches on either side

LEFT OUTER JOIN
A left outer join combines the ideas behind a left join and an outer join. Basically – if you use a left outer join you will get the records in the left table that DO NOT have a match in the right table.

RIGHT OUTER JOIN
A right outer join combines the ideas behind a right join and an outer join. Basically – if you use a right outer join you will get the records in the right table that DO NOT have a match in the left table.

I got this info from the net

Visual Explanation of Joins
‘Getting’ JOINS
Using MySQL JOINS
SQL JOIN
W3C Schools.com