-
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…
-
‘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’ )…
-
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…