Query Optimization

Hi All,

I am using MySql ver. 5.1.41.

I have the following table structure for storing apartment tariff within a date range.



CREATE TABLE IF NOT EXISTS `tariff` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `room_type_id` int(10) unsigned NOT NULL,
  `room_plan` char(3) NOT NULL,
  `date_from` date NOT NULL,
  `date_to` date NOT NULL,
  `tariff` mediumint(8) unsigned NOT NULL,
  `type` char(10) NOT NULL DEFAULT 'default',
  PRIMARY KEY (`id`)
 )ENGINE=MyISAM  DEFAULT CHARSET=utf8


where ‘room_type_id’ stand for different type of room and ‘room_plan’ stand for different room plan abbreviation like for room only its “EP”, with breakfast “CP” so on… and ‘type’ stand for tariff which is ‘default’ or ‘exception’.

here default i mean tariff for a range of dates say 01 JAN 2010 to 31 DEC 2010 and exception like where tariff changes like 15 MAY 2010 to 15 JUN 2010 or 16 JUN 2010 to 31 AUG 2010 in the example data for room_type_id = 2, you get a better understanding with the data i am supplying below…


INSERT INTO `tariff` (`id`, `room_type_id`, `room_plan`, `date_from`, `date_to`, `tariff`, `type`) VALUES
(1, 2, 'EP', '2010-01-01', '2010-12-31', 1000, 'default'),
(2, 2, 'CP', '2010-01-01', '2010-12-31', 1500, 'default'),
(3, 2, 'MAP', '2010-01-01', '2010-12-31', 2000, 'default'),
(4, 2, 'API', '2010-01-01', '2010-12-31', 2500, 'default'),
(64, 2, 'API', '2010-06-16', '2010-08-31', 2700, 'exception'),
(63, 2, 'MAP', '2010-06-16', '2010-08-31', 2200, 'exception'),
(62, 2, 'CP', '2010-06-16', '2010-08-31', 1700, 'exception'),
(61, 2, 'EP', '2010-06-16', '2010-08-31', 1200, 'exception'),
(58, 2, 'CP', '2010-05-01', '2010-06-15', 1600, 'exception'),
(59, 2, 'MAP', '2010-05-01', '2010-06-15', 2100, 'exception'),
(57, 2, 'EP', '2010-05-01', '2010-06-15', 1100, 'exception'),
(60, 2, 'API', '2010-05-01', '2010-06-15', 2600, 'exception'),
(17, 1, 'EP', '2010-01-01', '2013-06-30', 3453, 'default'),
(18, 1, 'CP', '2010-01-01', '2013-06-30', 6876, 'default'),
(19, 1, 'MAP', '2010-01-01', '2013-06-30', 4563, 'default'),
(20, 1, 'API', '2010-01-01', '2013-06-30', 7686, 'default'),
(49, 12, 'EP', '2010-03-01', '2013-11-10', 3453, 'exception'),
(50, 12, 'CP', '2010-03-01', '2013-11-10', 6575, 'exception'),
(51, 12, 'MAP', '2010-03-01', '2013-11-10', 3453, 'exception'),
(52, 12, 'API', '2010-03-01', '2013-11-10', 6786, 'exception');

for retrieving the tariff from the above table i am using the integer table trick, here is the ‘integer’ table structure and data:


CREATE TABLE IF NOT EXISTS `integers` (
  `i` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `integers` (`i`) VALUES
(0),
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9);


and with the below query i am able to get the tariff for a range of dates, like the below query return tariff between 14 JUN to 16 JUN 2010 both date including:



SELECT DATE_FORMAT(DATE_ADD( '2010-06-14', INTERVAL 10 * t.i + u.i DAY ), '%a') AS thedayname, DATE_ADD( '2010-06-14', INTERVAL 10 * t.i + u.i DAY) AS thedate, ht.room_plan, ht.tariff, ht.type FROM integers AS t CROSS JOIN integers AS u LEFT OUTER JOIN tariff AS ht ON DATE_ADD( '2010-06-14', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to WHERE DATE_ADD( '2010-06-14', INTERVAL 10 * t.i + u.i DAY ) <= DATE_SUB('2010-06-17', INTERVAL 1 DAY) AND ht.room_type_id = '2' ORDER BY thedate, ht.type, ht.tariff


you can run the above SQL code to test this, now my problem is that the above query returning both ‘default’ and ‘exception’ tariff, i want when there is exception tariff exist the query return only exception tariff not the default.

any help will be highly appreciated.

Kind Regards
Sanjeev.

(: is any query suggestions within my scenario :cool:

Hi rudy,

Thanks for your advice, i am taking care of the input with some unique index and at the time of data feeding also if i don’t do that with two types of tariff, data feeding goes very tedious job and this is not user friendly, thats why i am using this.

Do you suggest any query optimization within this scenario or any other query to retrieve data.

Thanks a lot for all your help.

Kind Regards
Sanjeev.

my advice is not to have two tariffs (default and exception) that overlap

i guess it depends on where you want the complexity

you’ve decided the complexity belongs in the retrieval, and i disagree

:slight_smile: