• MySQL

    Using Multiple Sequences in a Table

    Recently, I wanted to use multiple sequences in a table.  I wanted to generate a new sequence for every event for the id of its registrants. The table structure was something like this : CREATE TABLE registrants ( event_id INT NOT NULL, id INT AUTO_INCREMENT NOT NULL, name VARCHAR(100), email VARCHAR(100), address VARCHAR(100), PRIMARY KEY ( event_id, id ) ) ; But I ran into a problem. This works only for MyISAM and BDB tables. You can use AUTO_INCREMENT on a secondary column in a multiple-column index only for MyISAM and BDB tables. BDB isn’t supported any longer from MySQL 5.1 and I didn’t want to use MyISAM as my…

  • MySQL

    ‘EXISTS’ is better than ‘IN’

    While working on my last project, I was facing a query which was taking 30 second to execute. The number of tables involved were 10 and total records were around 20k.  It was a search functionality for a web-app project.  There were lot of sub-queries involved in the first draft, all of them using ‘IN’. After doing some research ( within MySQL Community ) , I just replaced all ‘IN’  by ‘EXISTS’ and voila !  The query took 11 seconds to execute. For example, you could change this query  : SELECT * FROM invitees i WHERE i.event_id IN ( SELECT e.id FROM event e WHERE e.city LIKE = ‘ABC’ )…

  • Coding Practice,  MySQL

    Use ANSI Style for MySQL

    I prefer to use ANSI style for writing MySQL queries.  When I started working on SQL, I used the old style aka Theta style for all my queries. Example of a query in  Theta style : SELECT  e.title, i.first_name, i.response FROM invitees i, events e WHERE e.id = i.event_id AND e.date = ‘2008-07-04’; which , in ANSI style is equivalent to : SELECT e.title, i.name, i.response FROM invitees i INNER JOIN events e on e.id = i.event_id WHERE e.date = ‘2008-07-04’ Many developers prefer using the old style. But believe me, ANSI style is lot better and advantageous. First, MySQL  supports OUTER JOINs in ANSI style. Second, ANSI style forces…