The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 11): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `date` is indexed, the index won’t be used as it’s wrapped with the function `CONVERT`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 11): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `entrytime` is indexed, the index won’t be used as it’s wrapped with the function `CONVERT`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid Calling Functions With Indexed Columns (query line: 19): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `date` is indexed, the index won’t be used as it’s wrapped with the function `CONVERT`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
4. Avoid Calling Functions With Indexed Columns (query line: 19): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `entrytime` is indexed, the index won’t be used as it’s wrapped with the function `CONVERT`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
5. Avoid Calling Functions With Indexed Columns (query line: 27): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `date` is indexed, the index won’t be used as it’s wrapped with the function `CONVERT`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
6. Avoid Calling Functions With Indexed Columns (query line: 27): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `entrytime` is indexed, the index won’t be used as it’s wrapped with the function `CONVERT`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
7. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `mylog` ADD INDEX `mylog_idx_loc_tag` (`loc`,`tag`);

The optimized query:

SELECT
        mvlog.loc,
        mvlog.edate,
        mvlog.tag,
        (SELECT
            COUNT(*) 
        FROM
            mylog AS ml 
        WHERE
            mvlog.loc = ml.loc 
            AND mvlog.edate = CONVERT(ml.date, DATEADD(ss, ml.entrytime, '19700101')) 
            AND mvlog.tag = ml.tag) AS visits,
        (SELECT
            SUM(ml2.entrywt - ml2.exitwt) 
        FROM
            mylog AS ml2 
        WHERE
            mvlog.loc = ml2.loc 
            AND mvlog.edate = CONVERT(ml2.date, DATEADD(ss, ml2.entrytime, '19700101')) 
            AND mvlog.tag = ml2.tag) AS consumed,
        (SELECT
            SUM(ml3.exittime - ml3.entrytime) 
        FROM
            mylog AS ml3 
        WHERE
            mvlog.loc = ml3.loc 
            AND mvlog.edate = CONVERT(ml3.date, DATEADD(ss, ml3.entrytime, '19700101')) 
            AND mvlog.tag = ml3.tag) AS occupancy 
    FROM
        eventlogV AS mvlog WITH (INDEX (pt_index))