No sorry,
the difference is that the DB that i have posted is not complete with data from tipo 5 and tipo 6.
For test just change with tipo = 1

Roy Lambert wrote:

Mirco

I've just tried here and with I6 added in I still get c10 seconds. I suspect that you've somehow lost the indices I said to add in.

However, I suspect that you need to go back to the beginning and see why you're doing this and if there's a better way.

Roy Lambert

New data

Mirco


Downloaded it, restored the database and ran the query without any changes. Its taking ages. When it finally finishes I start looking for the cause.
Roy Lambert

Mirco


1. I think you need to open a support ticket with Tim about this - I just can't make out what's happening, apart from the fact that it seems its something to do with the table interm.
The key factor (I think) is

Total rows visited: 3606 for i1
Total rows visited: 6459 for i1 and i2
Total rows visited: 60033 for i1..i3
Total rows visited: 1114013 for i1..i4

I cancelled t i1..i5, and when I did it was
Total rows visited: 23480459

but for the temporary table (as below)  for i1..i6 its
Total rows visited: 3471

I have absolutely no idea what's going on which is why I suggest opening a support ticket.

2. whilst not ideal creating a temporary table give a solution

DROP  TABLE Trial! <<<<< only needed if it exists

CREATE TEMPORARY TABLE Trial AS
SELECT DISTINCT idGiro,tipo,tempo from interm
WHERE idGiro in (SELECT idx from giri)
GROUP BY idGiro,tipo
WITH DATA!

CREATE INDEX idx1 ON Trial (idgiro)!
CREATE INDEX idx2 ON Trial (tipo)!
CREATE INDEX idx3 ON Trial (tempo)!

This dropped the time taken to less than a second for the query with I1..I6 plus maybe 5 seconds for the creation of the temporary table. You could probably speed it up by using an in-memory database for the temporary table.

Roy Lambert

Mirco

I am guessing that the query relates to bicycle races (giro), tips (tipo) and times (tempo).

It appears that the tables 'giri' and 'interm' are essentially the same.

It you redesigned the DB so that it is is normalised, you will find that you will get the results you are wanting (or the results that I think you are wanting) much more elegantly.

'giri' table: Combine 'giri' and 'interm' tables with a flag to indicate interim results. Key 'giro'. Remove all data that relates only to 'tips'.
'tip' table: Table containing only tip information. Key 'giro', 'tipo'. Remove all data that relates only to 'giri'

The SELECT statement is invalid as it contains columns that are unrelated to the GROUP clause.

Richard