How to get average of views before clicking on advertisement

Ok, here's my two pence:

SELECT header.Col1,
       MAX((CASE WHEN details.ordinal=1 THEN details.Col2 END)) AS row1,
       MAX((CASE WHEN details.ordinal=2 THEN details.Col2 END)) AS row2,
    -- ... and so on..
       MAX((CASE WHEN details.ordinal=99 THEN details.Col2 END)) AS row99

FROM (
      --- For each Col1, enumerate all the rows and return Col2 as well:
      SELECT Col1, Col2,
             ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Dumb_ID) AS ordinal
      FROM someTable
      ) AS details

--- join this to the header:
INNER JOIN someTable AS header ON details.Col1=header.Dumb_ID

--- One row per header:
GROUP BY header.Col1;

Admittedly, not the prettiest code I've ever written. How it works:

• The "header" in this case is the row where Dumb_ID='1A!'.
• "details" contains one row for each record that can be joined to the header ON header.Dumb_ID=details.Col1, in this example rows where Col1='1A!'.
• The "details" subquery is given an ordinal, a ROW_NUMBER(), that enumerates each row (partitioned by Col1).
• "header" and "details" are joined and everything is output by aggregating the result, so we get a single record for each "header"
• Each "detail" is assigned to its own column using a CASE and the ordinal number. We need to use MAX() (or any other aggregate function, really) here so it'll work with the aggregate.

I hope I've understood your question correctly. Also, note that this is ad-hoc coded, without testing. Let me know how it works out.

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly.

Let's take your query and look for 'tom'

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE c.castName LIKE '%tom%';

This joins everything. Then, it scans the resulting temp table and returns every row with tom.

Let's implement the FULLTEXT method

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

I can almost assure you that a full table scan will occur during the join because I have experienced it hundreds of times:

• Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
• Jan 26, 2012 : Mysql fulltext search my.cnf optimization
• May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
• Jul 18, 2012 : Why full-text-search returns less rows than LIKE

I started writing posts about FULLTEXT and query refactoring ever since I saw a post from webmasterworld.com entitled MySQL Match Against + a very expensive join. How do I optimize this?

Given the information I just mentioned let's take your query and refactor it to handle the FULLTEXT and stilldo a good join.

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

First, apply the MATCH ... AGAINST operator to cast_movie and return ImdbId and other fields

SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)

Make that a subquery to provide keys to join and retrieve data from film_info

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM
(
    SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
    WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)
) as c JOIN film_info as f ON c.ImdbId = f.ImdbId;

This should produce a better result.

If your tables use InnoDB Storage Engine, remember to tune the InnoDB Fulltext options. Any fulltext option that does not start with innodb_ is meant for MyISAM (See my post MySQL FullText search on string shorter than 3 chars returns no rows)

Give it a Try !!!