Optimize Join sentence with foreign keys, and show records with nulls

Question

Hi guys I have the following structure

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `sis_param_tax` (
  `id` int(5) NOT NULL auto_increment,
  `description` varchar(50) NOT NULL,
  `code` varchar(5) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7;

CREATE TABLE IF NOT EXISTS `sis_param_city` (
  `id` int(4) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `sis_supplier` (
  `id` int(15) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL,
  `address` varchar(200) default NULL,
  `phone` varchar(30) NOT NULL,
  `fk_city` int(11) default NULL,
  `fk_tax` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `fk_city` (`fk_city`),
  KEY `fk_tax` (`fk_tax`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

ALTER TABLE `sis_supplier`
  ADD CONSTRAINT `sis_supplier_ibfk_4` FOREIGN KEY (`fk_tax`) REFERENCES `sis_param_tax` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `sis_supplier_ibfk_3` FOREIGN KEY (`fk_city`) REFERENCES `sis_param_city` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

My questions are

1. This structure allows me to have a supplier with city and tax fields = null (in case user didn't set these values). Right?

2. If I delete "X" city, supplier's fk_city with city="X" are set to null, same with fk_tax. Right?

3. I want to optimize (IF POSSIBLE) the following join sentence, so I can show suppliers whom have fk_city and/or fk_tax = NULL

SELECT DISTINCT 
  sis_supplier.id,
  sis_supplier.name,
  sis_supplier.telefono,
  sis_supplier.address,
  sis_supplier.phone,
  sis_supplier.cuit,
  sis_param_city.name AS city,
  sis_param_tax.description AS tax,
  sis_supplier.fk_city,
  sis_supplier.fk_tax
FROM
  sis_supplier 
  LEFT OUTER JOIN sis_param_city
  ON
  sis_supplier.`fk_city` = sis_param_city.id
  LEFT OUTER JOIN `sis_param_tax`
  ON
  sis_supplier.`fk_tax` = `sis_param_tax`.`id`

Thanks a lot in advance,

Answer 1

if you want to show records with nulls than use RIGHT or LEFT JOIN
depend on your needs

Answer 2

1. Yes.

2. Yes.

3. Yes, it's good to optimize. The query you showed looks fine. How is it not working for you?

---

Have you analyzed the query with EXPLAIN? This can help you tell when you have a query that isn't using indexes effectively. In fact, all of Chapter 7 Optimization would be recommended reading.