Tag: sql

Entries for tag "sql", ordered from most recent. Entry count: 2.

Pages: 1

# Optimizing Subqueries in MySQL

Wed
08
Feb 2012

I'm not SQL guru, but I have to use databases from time to time, whether at home or at work, for desktop or (mostly) web applications. Some time ago I discovered a trick which greatly optimizes performance of complex queries that contain subqueries. I used it again recently when coding new comments administration panel for this website.

In my previous job I had a case when after several months of work my system gathered so much data that it couldn't generate report in any reasonable time. After applying this optimization reports from same data were generated instantly.

I know for these who deal with databases every day this "trick" or "optimization" is probably very basic, but if you - just like me - do only simple database tasks, it can be new to you. Here are the details:

Let's say we code a blog CMS and we have two tables:

CREATE TABLE posts (
    post_id int(11) NOT NULL AUTO_INCREMENT,
    title varchar(128) NOT NULL,
    content text NOT NULL,
    time datetime NOT NULL,
    PRIMARY KEY (post_id),
    KEY time_k (time)
);

CREATE TABLE comments (
    comment_id int(11) NOT NULL AUTO_INCREMENT,
    post_id int(11) NOT NULL,
    author varchar(32) NOT NULL,
    text text NOT NULL,
    time datetime NOT NULL,
    PRIMARY KEY (comment_id),
    KEY post_id_fk (post_id),
    CONSTRAINT post_id_fk FOREIGN KEY (post_id)
        REFERENCES posts (post_id) ON DELETE CASCADE ON UPDATE CASCADE
);

Comments | #sql Share

# My Impressions about SQLite

Tue
20
Apr 2010

SQLite is a very strange library. It's a database engine that can store lots of data in a relational database and exposes API based on SQL language. On the other hand though, it's not a huge application that has to be installed in the system, work in the background and you have to connect to it via network interface, like MySQL or PostreSQL do. It's actually a lightweight library written in C that can be linked with your program and uses specified file as the database. It's fascinating that such a small library (there is even a preprocessed source version as a single 3.75 megabyte C file!) supports much of the SQL language.

The API of the SQLite library is similar to any other SQL-based database access API for any programming language. I've played with it a bit and here is my small sample code:

#include <sqlite3.h>

int main()
{
  sqlite3 *db;
  sqlite3_open_v2("D:\\tmp\\test.db", &db,
    SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

  sqlite3_exec(db, "begin", NULL, NULL, NULL);
  
  sqlite3_stmt *stmt;
  sqlite3_prepare_v2(db, "insert into table1 (id) values (?)",
    -1, &stmt, NULL);

  for (int i = 0; i < 10; i++)
  {
    sqlite3_reset(stmt);
    sqlite3_bind_int(stmt, 1, i);
    sqlite3_step(stmt);
  }

  sqlite3_exec(db, "commit", NULL, NULL, NULL);

  sqlite3_finalize(stmt);

  sqlite3_close(db);
}

It's hard for me to think of any application for such a strange library. It offers too much when you just want to design your file format and too few if you need a fully-featured database, like for a web server. So why did I decide to get to know this library? It's all because of an interesting gamedev tool called Echo Chamber. It's a free data mining program that can visualize data from SQLite database files as many different kinds of plots, even 3D ones. So when you integrate logging some numeric data from your engine into an SQLite database you can easily do performance analysis with it.

Comments | #tools #libraries #sql Share

Pages: 1