SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code:
    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...

    Code:
    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:

    Code:
    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:

    Code:
    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    my advice is not to have two tariffs (default and exception) that overlap
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by sanjeev View Post
    data feeding goes very tedious job and this is not user friendly
    i guess it depends on where you want the complexity

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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is any query suggestions within my scenario


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •