database
Useful PHP and MySQL articles to improve your development skills!
CakePHP Authsome
Authentication for people who hate the AuthComponent.
The 20 best practices when using MySQL
Just came across this great article with 20 best practices when using MySQL: MySQL best practices.
Be sure to check them, the following items are in the article:
Optimize Your Queries For the Query Cache
LIMIT 1 When Getting a Unique Row
Get Suggestions with PROCEDURE ANALYSE()
Prepared Statements
Split the Big DELETE or INSERT Queries
Be Careful with Persistent Connections
And more [...]
Using TOP in Oracle queries
We already discussed the methods to use TOP functions in MySQL and in MSSQL.
In Oracle we make use of the ROWNUM function in the WHERE clause.
To take our names table again in this example, to get the first 5 rows, make use of the following query:
SELECT firstname,lastname
FROM names
WHERE ROWNUM <= 5
This will retrieve the first [...]
Using TOP in MySQL queries
On the web I see a lot of questions of developers who are wanting to know how they can retrieve only the first N records in their queries.
The answer is simple, LIMIT
For example, if you only want to select the top 5 names from a names tables, use the following query:
SELECT `firstname`,`lastname`
FROM `names`
ORDER [...]
Usefull MySQL articles and tutorials to improve your skills!
As promised in my previous post with usefull PHP articles and tutorials, now it’s time for some cool and handy SQL articles!
Here we go:
Execute SQL job through batch file
This article will show you how you can execute a SQL job with one click, allowing anyone to do this.
Author Divya Agrawal shows a technique [...]
How to: Computed columns in SQL Server
Last week I was playing around with the computed columns feature in SQL Server from Microsoft.
A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression [...]
MySQL: Performance benchmark MyISAM versus InnoDB
Because I’m running a project which has a relative large database (tables with 70.000.000 records in them), I wanted to know which was faster in performance, MyISAM or InnoDB.
Currently I’m using MyISAM, and I’m running into some performance and stability issues.
First, I’ve created a table with a few columns:
MySQL Event Scheduler – A good replacement for cron
We all know about cron, an easy way to schedule certain processes, like truncating your log tables in your MySQL database every week.
With MySQL 5.1 the guys at MySQL introduced a new cool feature: The MySQL Event Scheduler !
With the Event Scheduler you can schedule tasks that you want to perform on your database.
This is great [...]