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.