2 added 530 characters in body
source | link

Here is a suggestion: add an index on (seriesName, retreivalTime) and try this query. It won't be super fast but probably more efficient than what you have:

SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT seriesName, 
             MAX(retreivalTime) AS max_retreivalTime
      FROM DataItems
      GROUP BY seriesName
      ORDER BY max_retreivalTime DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.seriesName = d.seriesName
    AND di.max_retreivalTime = d.retreivalTime
ORDER BY di.max_retreivalTime ;

Or (variation) using the PK as well, with an index on (seriesName, retreivalTime, dbId) and query:

SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT dbId
      FROM DataItems
      GROUP BY seriesName
      ORDER BY MAX(retreivalTime) DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.dbId = d.dbId
ORDER BY d.max_retreivalTime ;

The logic behind the queries is to use only the index for the derived table calculations (finding the max(retreival-time) for every seriesName and then order by and do the offset-limit thing.)

Then the table itself will be involved only for fetching those 200 rows that are to be displayed.

Here is a suggestion: add an index on (seriesName, retreivalTime) and try this query. It won't be super fast but probably more efficient than what you have:

SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT seriesName, 
             MAX(retreivalTime) AS max_retreivalTime
      FROM DataItems
      GROUP BY seriesName
      ORDER BY max_retreivalTime DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.seriesName = d.seriesName
    AND di.max_retreivalTime = d.retreivalTime
ORDER BY di.max_retreivalTime ;

Here is a suggestion: add an index on (seriesName, retreivalTime) and try this query. It won't be super fast but probably more efficient than what you have:

SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT seriesName, 
             MAX(retreivalTime) AS max_retreivalTime
      FROM DataItems
      GROUP BY seriesName
      ORDER BY max_retreivalTime DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.seriesName = d.seriesName
    AND di.max_retreivalTime = d.retreivalTime
ORDER BY di.max_retreivalTime ;

Or (variation) using the PK as well, with an index on (seriesName, retreivalTime, dbId) and query:

SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT dbId
      FROM DataItems
      GROUP BY seriesName
      ORDER BY MAX(retreivalTime) DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.dbId = d.dbId
ORDER BY d.max_retreivalTime ;

The logic behind the queries is to use only the index for the derived table calculations (finding the max(retreival-time) for every seriesName and then order by and do the offset-limit thing.)

Then the table itself will be involved only for fetching those 200 rows that are to be displayed.

1
source | link

Here is a suggestion: add an index on (seriesName, retreivalTime) and try this query. It won't be super fast but probably more efficient than what you have:

SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT seriesName, 
             MAX(retreivalTime) AS max_retreivalTime
      FROM DataItems
      GROUP BY seriesName
      ORDER BY max_retreivalTime DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.seriesName = d.seriesName
    AND di.max_retreivalTime = d.retreivalTime
ORDER BY di.max_retreivalTime ;