Blogs about sql:
It's Not Me. It's Them.
3.10.2008
Check out this MySQL bug. It's exactly what I was trying, and failing, to do this evening. Ha! For once, it's not a mistake on my part.
I'm pretty sure that the trouble I've been having with configuring my .htaccess file is all my fault though. I did manage to get it working for now, but only through the voodoo of re-typing the code. I sure hope my O'Reilly books arrive to soon to reveal the secrets.
0 comment(s).
Permalink
SQL Optimization Tips.
9.19.2007
In honor of International Talk Like A Pirate Day, Pew Pew Laser Blog brings you this message from the SQL Pirate.
Yar me heaties! I were diggin' around in me old booty-chest o' code and I found this here venerable list of SQL tips. These here tips be for keepin' yer SQL queries runnin' faster than me crew towards the last bottle o' rum. Keep it close to ye vest.
- Obviously, putting indexes on your destination tables will help your queries to run faster. Remeber to also check your source and datamart tables for appropriate indexes.
- You can have several columns indexed separately, or combine them into a single index. The SQL engine will typically combine indexes as needed to run queries.
- “Be positive in your queries.” WHERE IN is faster than WHERE NOT IN. You can use MINUS (similar to UNION ALL) to exclude rows from your results. MINUS is also good for comparing the differences between 2 tables or result sets.
- Suppose you have a large subquery, and some excluding conditions such as account number or date that require a join. It is much more efficient to use those excluding conditions inside your subquery rather than outside it.
- Functions are your friend. They are fast, fast, fast and reuseable.