SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query optimisation help please.

    Hello,

    I am querying as shown below. query takes 2.6 seconds and there are +/- half million records in the statistics table and 200 odd in the business_details table

    indexes are as shown. what need I do to make the query faster, please?

    create statement
    Code MySQL:
    CREATE TABLE statistics
    ( Statistic_ID int(11) unsigned zerofill NOT NULL auto_increment
    , Session_ID varchar(64)
    , Browser varchar(256) 
    , Country_of_Connection char(3) 
    , Time_Into_Site datetime NOT NULL default '0000-00-00 00:00:00'
    , Time_of_view datetime NOT NULL default '0000-00-00 00:00:00'
    , Site_The_Visitor_Came_From varchar(64)
    , Referer varchar(128)
    , Domain_Viewed varchar(64)
    , Page_Viewed varchar(64)
    , `Subject` varchar(50) 
    , `File` varchar(32)
    , Main_Business_ID int(11) 
    , Nearby_Business_ID int(11) 
    , PRIMARY KEY  (Statistic_ID)
    , KEY Domain_Viewed (Domain_Viewed)
    , KEY Session_ID (Session_ID)
    ) ENGINE=InnoDB  DEFAULT CHARSET=latin1;


    query
    Code MySQL:
    select 
             stat.Domain_Viewed
        from
          statistics AS stat
      inner 
         join business_details AS bd
           on bd.web_url = stat.Domain_Viewed 
      where bd.business_id = 1
         group
           by stat.Session_ID

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I should add that the result set is of 61525 rows and a result set of 4537 rows takes 0.2316 secs

    bazz

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    doh!! I think I got it, though I am not getting the time it takes any more from phpMyAdmin. I'll set up heidiSQL again and see if it gives me that.

    Code MySQL:
    select count(*)
       from
       statistics AS stat
     inner 
        join business_details AS bd
          on bd.web_url = stat.Domain_Viewed 
       where bd.business_id = 1
    .

    bazz


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
  •