Tag Archives: MySQL

Delete rows from multiple tables

So I was puttering along with a project I came across a situation I have come across before. How to delete information from a table that is related, such as deleting a user, their profile, and any other information associated with said user. In the past I had just executed as many queries as it took to get the job done. This isn’t the best option though for one big reason.

Performance

The more results a query returns, the more system resources it uses. So executing multiple queries can often times end up with the server doing more work than necessary. So my suggestion is to create a multi-table query that narrows the results using the where clause.

Imagine a scenario where you had the following tables, keep in mind the tables are in no way what I would use for an actual site, they are far from being normalized:

CREATE TABLE `User_Info` (
  `User_ID` int(11) NOT NULL auto_increment,
  `User_Type_ID` int(11) NOT NULL,
  `User_Title` varchar(255) NOT NULL,
  `User_Address_ID` int(11)NOT NULL,
  `User_Profile_ID` int(11)NOT NULL,
  PRIMARY KEY  (`User_ID`)
  FOREIGN KEY  (`User_Address_ID`)
  FOREIGN KEY  (`User_Profile_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;
CREATE TABLE `User_Address` (
  `User_Address_ID` int(11) NOT NULL auto_increment,
  `User_Address` varchar(100) NOT NULL,
  `User_Town` varchar(100) NOT NULL,
  `User_State` varchar(100) NOT NULL,
  `User_ID` varchar(100) NOT NULL,
  PRIMARY KEY  (`User_Address_ID`),
  FOREIGN KEY (`User_ID`),
) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;
CREATE TABLE `User_Profile` (
  `User_Profile_ID` int(11) NOT NULL auto_increment,
  `User_Name` varchar(100) NOT NULL,
  `User_Password` varchar(100) NOT NULL,
  `User_ID` varchar(100) NOT NULL,
  PRIMARY KEY  (`User_Profile_ID`),
  FOREIGN KEY (`User_ID`),
) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;

The acceptable solution

My old method would have had me doing multiple queries

DELETE FROM User_Info WHERE User_ID=1;
DELETE FROM User_Address WHERE User_ID=1;
DELETE FROM User_Profile WHERE User_ID=1;

The better solution

The better way to do this is of course as I said above using one query.

DELETE FROM User_Info, User_Address, User_Profile
USING User_Info
WHERE User_Address.User_ID = User_Info.User_ID
AND User_Profile.User_ID = User_Info.User_ID
AND User_Info.User_ID =  1;

If you are interested in learning other methods of doing multi-table deletes, check out this site that uses joins to accomplish the same result.

Copy info between database tables with one query

I was thinking about the future of my web site the other day. One of the questions I began to think about was, what if I decide to change to a different Content Management System (CMS)? Which was never really a problem before because I did not have much content to move over. In the past I had pages for my portfolio, about me, contacting me, and that is about it. Now that I have begun to write on my site as well, I now have to worry about how to preserve the content. The two areas I am concerned with are:

  • Moving data from one database to another.
  • How to handle URL rewriting so that my pages don’t suddenly go AWOL in search engines.

Set-up

Say you have an existing table in a database called DB1,

CREATE TABLE `Event_Info` (
  `Event_ID` int(11) NOT NULL auto_increment,
  `Event_Type_ID` int(11) NOT NULL,
  `Event_Title` varchar(255) NOT NULL,
  `Event_Date` varchar(100) NOT NULL,
  `Event_Address` varchar(100) NOT NULL,
  `Event_Town` varchar(100) NOT NULL,
  `Event_Desc` longtext NOT NULL,
  `Event_Inserted` varchar(100) NOT NULL,
  `Event_Updated` varchar(100) NOT NULL,
  `User_ID` int(11) NOT NULL,
  `Event_Pending` varchar(255) NOT NULL default '0',
  PRIMARY KEY  (`Event_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=latin1;

You are tasked with moving over the id, title, town, and description to a new database/table to be used with a different application. I put together this test table to move the information into, it is located in a database named DB2.

CREATE TABLE `event_stuff` (
  `eventID` int(11) NOT NULL auto_increment,
  `eventTitle` varchar(255) NOT NULL,
  `eventTown` varchar(255) NOT NULL,
  `eventDesc` varchar(255) NOT NULL,
  PRIMARY KEY  (`eventID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Moving the data

So what we need to do is select the title, town, and description from Event_Info and insert it into event_stuff. The first step is to write a query that selects the data from Event_Info. In the past when I have transferred info between two tables I have used a subquery to get the values to insert. Something along the lines of this, INSERT INTO Table_One VALUES(SELECT field1,field2,field3 FROM Table_Two). I tried to set something like this up and ran into one problem, I am working not between tables but between databases.

The query

After reading some documentation on the MySQL site and spending a couple hours working out queries. I figured that I must need to reference the database somehow but I really could not find adequate information about it and so I finally gave in and went to Stackoverflow. There I found out that I didn’t need the schema files as I thought I might have and all I needed to do was reference the database. The final query that worked flawlessly for me was

INSERT INTO DB2.event_stuff (eventTitle,eventTown,eventDesc) 
SELECT t.Event_Title, t.Event_Town, t.Event_Desc FROM DB1.Event_Info t;

Something I learned from this query is that you can set an alias for a table after you use it! I had always been under the impression it was like PHP, PERL, ASP, etc… in that you needed to instantiate the variable before using it. I wonder if MySQL reads the whole query before executing it? Anyway, if you have any input let me know, if there is a more efficient way to do this with one query I would be interested to see that.

Part two will cover URL rewriting so there isn’t a loss in ranking with the search engines.

MySQL notes

It has been some time since I took my intro to Relational Databases back in the spring of
07. So today I decided to do some research on MySQL and the database in general. So without further adieu, here are some helpful notes.

Data Types

  1. char vs varchar – If a string is inserted of 20 character in length is inserted into a char(30) field, it will take up 30 characters in the database. If that same string 20 characters in length is inserted into a varchar(30) field, it will take up just 20 characters of space.
  2. Int vs smallint – Smallint is used if your number will not exceed the range of -32768 – 32768. Int is used when the number falls between -2147483648 – 2147483648. It is also worthy to mention the other types of integers, tinyint, mediumint, and bigint

Normalization

Database normalization is quite possibly one of the blurriest areas for me. Going from teachers that profess a web site needs to be normalized to other teachers that say ‘if it works don’t fix it’ I have had to find a middle ground for now until I can make a decision and learn more about it. For most projects I do, I follow through to third normal form (3NF). This prevents from having redundancy, duplicate data, as well as prevents data anomalies from occurring. Data anomalies are incorrect/inconsistent data that is the result of updates or insertions that create “duplicate” records.

On another note, I just purchased a new MySQL book MySQL Crash Course by Ben Forta. It looks to be an excellent replacement for my current book. Just looking through the table of contents and reading a few pages, it covers way more than my previous book I purchased for my relational database class. So stay tuned for MySQL Notes ver.2.