SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    please help me to optimise this sql query, it taks long time to return result.

    please help me to optimise this sql query, it taks long time to return result.

    SELECT count( pagerno ) AS total
    FROM tbldetails s1
    WHERE s1.ccode = '07122009012807953'
    AND s1.col1 <> 'invalid'
    AND s1.col6 <= '2009-12-15'
    AND s1.col6 = (
    SELECT MAX( s2.col6 )
    FROM tbldetails s2
    WHERE s1.pagerno = s2.pagerno
    AND s2.ccode = '07122009012807953')

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Which columns of this table have you defined indexes on already?

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    Scotland
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didnt not create any index on this table, after you mention i just create one on detailid. still the same amount of time take, when i execute same the query.


    CREATE TABLE IF NOT EXISTS `tbldetails` (
    `detailid` varchar(30) character set utf8 collate utf8_unicode_ci default NULL,
    `ccode` varchar(30) character set utf8 collate utf8_unicode_ci default NULL,
    `pagerno` varchar(1000) character set utf8 collate utf8_unicode_ci default NULL,
    `months` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,
    `price` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,
    `disc` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,
    `tprice` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,
    `sdate` date default NULL,
    `edate` date default NULL,
    `status` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,
    `cdtby` varchar(60) character set utf8 collate utf8_unicode_ci default NULL,
    `mdyfy` varchar(60) character set utf8 collate utf8_unicode_ci default NULL,
    `remarks` text,
    `col1` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,
    `col2` varchar(1) character set utf8 collate utf8_unicode_ci default NULL,
    `col3` varchar(10) character set utf8 collate utf8_unicode_ci default NULL,
    `col4` datetime default NULL,
    `col5` varchar(20) character set utf8 collate utf8_unicode_ci default NULL,
    `col6` datetime default NULL,
    `col7` varchar(20) character set utf8 collate utf8_unicode_ci default NULL,
    `col8` varchar(20) character set utf8 collate utf8_unicode_ci default NULL,
    `col9` varchar(20) character set utf8 collate utf8_unicode_ci default NULL,
    KEY `t_idx` (`detailid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    detailid isn't a column you use anywhere in your query, so adding an index there won't help

    Indexes on the columns you do use (ccode, col1, col6, pagerno) would


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
  •