mysql big query performance improvement

Question

Anyone know how I can optimise this query? It works but it takes so long that it causes server timeouts when there's a heavy load.

$screenRestrict="clientID='1' AND screenID='1'";
$lastupdate="1318515710";

INSERT INTO allvisits (clientID, screenID, hitID, entryPageID, entryPageName, xentryTime, xexitTime, pagecount, minsonsite)(
    SELECT 
        clientID, screenID, id AS hitID, pageID AS entryPageID,
        (SELECT name FROM pages WHERE id=entryPageID) AS entryPageName,
        (SELECT clicktime FROM clicks WHERE id = hitID AND isFirstClick=1 ) AS xentryTime,
        (SELECT MIN(clicktime) FROM clicks WHERE $screenRestrict AND isLastClick=1 AND clicktime > xentryTime) AS xexitTime,
        (SELECT COUNT(*) FROM clicks WHERE $screenRestrict AND clicktime BETWEEN xentryTime AND xexitTime) AS pagecount,
        (SELECT (xexitTime-xentryTime)/60) AS minsonsite
    FROM clicks WHERE $screenRestrict AND isFirstClick=1 AND clicktime>'$lastupdate'
)

Thanks a lot :)

Update:

Thanks to all for the tips. I've added an index to isLastClick and managed to speed it up a good deal but it still takes +10 seconds on a low server load. I've identified the last bottleneck and marked it below. Is there any better way to select the first "isLastClick" record that is later than xentrytime?

SELECT clientid, 
             screenid, 
             id                                                      AS hitid, 
             pageid                                                  AS entrypageid, 
             clicktime                                               AS xentrytime, 
             (SELECT name 
                FROM   pages 
                WHERE  id = entrypageid)                               AS entrypagename, 
             (SELECT clicktime 
                FROM   clicks 
                WHERE  clicktime > xentrytime //<<removing this cuts 8.5 seconds!!
                             AND screenid = '2' 
                             AND islastclick = 1 
                             LIMIT 1)             AS xexittime, 
             (SELECT COUNT(1) 
                FROM   clicks 
                WHERE  screenid = '2' 
                             AND clicktime BETWEEN xentrytime AND xexittime) AS pagecount, 
             (SELECT ( xexittime - xentrytime ) / 60)                AS minsonsite 
FROM   clicks 
WHERE  screenid = '2' 
             AND isfirstclick = 1 
             AND clicktime > '1318961057'

Answer 1

please check for comments.

if $screenrestrict and page id are same for a page. try to join the inner tables with that page id

I am not sure of your idea about xentrytime and xexittime columns. because, i see feilds fetched using sub query for the same thing.

Just remember, for tuning a query try to attain a filter and restrict the number of rows selected in each sub query. Some filters are written based on business logic, so think of including those condition also when u write a query, and see if performance improves in explain plan.

INSERT INTO allvisits 
            (clientid, 
             screenid, 
             hitid, 
             entrypageid, 
             entrypagename, 
             xentrytime, 
             xexittime, 
             pagecount, 
             minsonsite) 
(SELECT clientid, 
        screenid, 
        id                                                      AS hitid, 
        pageid                                                  AS entrypageid, 
        (SELECT name 
         FROM   pages 
         WHERE  id = entrypageid)                               AS entrypagename 
        , 
        clicktime                        AS xentrytime, //this should work w.r.t your code
        (SELECT MIN(clicktime)             //try to change this logic. The logic written here doesnt look good at all. and try to filter out data by joining with outer table. 
         FROM   clicks 
         WHERE  $screenrestrict 
                AND islastclick = 1 
                AND clicktime > xentrytime)                     AS xexittime, 
        (SELECT COUNT(1)   //this will give some performance improvement
         FROM   clicks 
         WHERE  $screenrestrict 
                AND clicktime BETWEEN xentrytime AND xexittime) AS pagecount, 
        (SELECT ( xexittime - xentrytime ) / 60)                AS minsonsite 
 FROM   clicks 
 WHERE  $screenrestrict 
        AND isfirstclick = 1 
        AND clicktime > '$lastupdate') 

Updated

These 2 inner queries need some more of fine tuning

       SELECT MIN(innClick.clicktime)             
         FROM   clicks innClick1
         WHERE  innClick1.screenid =  '2' // when u put it as part of big query use  WHERE  innClick1.screenid = outClick.screenid instead of hard coding it. where outClick is alias for Click table in outside
                AND innClick1.islastclick = 1 
                AND innClick1.clicktime > innClick1.xentrytime

        SELECT COUNT(1) 
         FROM   clicks innClick2
         WHERE  innClick2.screenid =  '2' 
                AND innClick2.clicktime BETWEEN innClick2.xentrytime AND innClick2.xexittime

w.r.t explain plan

• You can try to add more filter to reduce number of rows fetched in inner query.
• try to reduce any full table scan, and if the scan is based on index scan (full also) can be ignored for time being.
• try to add filters based on the columns that are already indexed.
• try to do a lot of trial and error by filtering data further in these sub-queries and -
• try to reduce the time in each query separately. and then finally join them when u have a better performance

The clicktime, xentrytime and xexittime if indexed will yeild better performance for the query i guess. you can try it , but still indexing will slow your insert statements as those extra index should be updated each time an insert happen.