Category Archives: mysql

python slice and sql every Nth row

Posted on April 24, 2012 by timwarnock

I would like to retrieve every Nth row of a SQL table, and I would like this accessed via a python slice function. A python slice allows access to a list (or any object that implements a __getitem__ method) by … Continue reading →

Posted in data arch., mysql, oracle, python | Comments Off

nvl, ifnull, nullif, isnull, coalesce

Posted on January 17, 2012 by timwarnock

I would like to programmatically handle NULL values in a database. If I add or multiply a known value to a NULL, the result is still NULL. This is an appropriate result as a NULL value in a database schema … Continue reading →

Posted in data arch., mysql, oracle | Comments Off

oracle, limit results and pagination

Posted on January 11, 2012 by timwarnock

I would like to limit query results in oracle to support pagination. In postgres, mysql, and sqlite you can use the LIMIT and OFFSET modifier, e.g., mysql> SELECT login FROM users ORDER BY login LIMIT 2 OFFSET 70001; +-------------+ | … Continue reading →

Posted in data arch., mysql, oracle | Leave a comment

sql, users not in group

Posted on July 19, 2011 by timwarnock

I would like to find all users not in a specific group, given the following database schema: CREATE TABLE foobar_users ( user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) UNIQUE NOT NULL ); CREATE TABLE foobar_groups ( group_id INT UNSIGNED … Continue reading →

Posted in mysql | Leave a comment

database indexes and optimization

Posted on July 8, 2011 by timwarnock

Previously, I discussed database schema normalization. Next, I would like to add indexes and optimize a schema for high-volume production usage. Consider the following schema: CREATE TABLE foobar_users ( user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL ); … Continue reading →

Posted in data arch., mysql | Leave a comment

php DB adapter, revisited

Posted on July 7, 2011 by timwarnock

I would like to call mysql stored procedures as if they were local php functions. For example, let's use the following mysql stored procedure, DELIMITER // CREATE PROCEDURE `usersByPage`(page_num INT, per_page INT) BEGIN SET @lim_start = (page_num - 1) * … Continue reading →

Posted in mysql, php, software arch. | Leave a comment

mysqldump, tips and tricks

Posted on July 5, 2011 by timwarnock

I want to backup a mysql database. The easiest approach is using mysqldump with its default options, i.e., # mysqldump -u user -h host -ppass db > backup.sql This will dump the full DDL and DML needed to re-create the … Continue reading →

Posted in mysql, shell tips | Leave a comment

Denormalization?

Posted on July 3, 2011 by timwarnock

Previously, I normalized my tables to Sixth Normal Form (6NF). Now, I want to consider denormalizing, if and when it's appropriate. Denormalizing is different than an un-normalized schema, which is never, ever, recommended. Strategies for denormalization appear in data-warehousing designs, … Continue reading →

Posted in data arch., mysql | Leave a comment

Normalization, 6NF

Posted on July 3, 2011 by timwarnock

Previously, I normalized my tables to Fifth Normal Form (5NF). Now, I want to continue to normalize such that the tables in my schema are in Sixth Normal Form (6NF). 6NF requires that each table satisfies only trivial join dependencies. … Continue reading →

Posted in data arch., mysql | Leave a comment

Normalization, 5NF

Posted on July 3, 2011 by timwarnock

Previously, I normalized my tables to Fourth Normal Form (4NF). Now, I want to continue to normalize such that the tables in my schema are in Fifth Normal Form (5NF). 5NF requires that every join dependency in a table is … Continue reading →

Posted in data arch., mysql | Leave a comment

Normalization, 4NF

Posted on July 3, 2011 by timwarnock

Previously, I normalized my tables to Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF). Now, I want to continue to normalize such that the tables in my schema are in Fourth Normal Form (4NF). 4NF requires for any non-trivial … Continue reading →

Posted in data arch., mysql | Leave a comment

Normalization, 3NF

Posted on July 3, 2011 by timwarnock

Previously, I normalized my table to Second Normal Form (2NF). Now, I want to continue to normalize such that the tables in my schema are in Third Normal Form (3NF). 3NF requires that every non-prime attribute is directly dependent on … Continue reading →

Posted in data arch., mysql | Leave a comment

Normalization, 2NF

Posted on July 2, 2011 by timwarnock

Previously, I normalized my table to First Normal Form (1NF). Now, I want to continue to normalize such that the tables in my schema are in Second Normal Form (2NF). 2NF requires that all non-key attributes depend on the whole … Continue reading →

Posted in data arch., mysql | Leave a comment

Normalization, 1NF

Posted on July 2, 2011 by timwarnock

I want to normalize a database schema for efficient transactions. I want to make sure all tables in the schema are in First Normal Form (1NF). There is no universal definition of 1NF; some definitions require only atomicity such that … Continue reading →

Posted in data arch., mysql | Leave a comment

last_insert_id()

Posted on June 29, 2011 by timwarnock

I would like to retrieve the id of a row I just inserted. In Oracle this problem is handled with the use of sequences, SQL> SELECT uid_seq.NEXTVAL INTO user_id FROM DUAL; SQL> INSERT INTO users (id, username) VALUES (user_id, 'joebob'); … Continue reading →

Posted in data arch., mysql, php | Leave a comment