"Denis St-Michel" <ds*******@aei. ca> wrote in message
I finally been able to do what I Wanted by building my request this
way:
SELECT t1.id,t1.Ticket No,t1.evaluatio n,t1.Username FROM A t1 INNER
JOIN A t2 ON t1.TicketNo = t2.TicketNo GROUP BY t1.id, t1.TicketNo,
t1.evaluation, t1.Username HAVING t1.evaluation = MAX(t2.evaluati on);
and it seems to be working well.
Clever! Only thing is you have to repeat the select columns in the group
by, which means lots of typing if you have more columns.
But you mentioned you want to find ticket numbers where a certain Username
gave the maximum evaluation. For this I think the following should work:
SELECT t1.id,t1.Ticket No,t1.evaluatio n,t1.Username
FROM
A t1
INNER JOIN A t2 ON t1.TicketNo = t2.TicketNo
where t1.Username = 'Fergus'
GROUP BY t1.id, t1.TicketNo, t1.evaluation, t1.Username
HAVING t1.evaluation = MAX(t2.evaluati on)
;
It's important to know what you're after, as you'd create different indexes
for each scenario: find ticket nos for which Username gave the maximum
evaluation as you said in the original post, or find the Username for every
ticket no who gave the maximum evaluation. They differ by just a where
clause on Username = ?. but may require different indices to optimize.
I think it should be efficient if you have an index on a ( TicketNo,
evaluation desc, Username ). Hopefully the engine drives by t2 on this
index, quickly finding the maximum evaluation for every ticket no, joins to
t1 on the same index using all three columns of the index. But running the
SQL in the control center shows it drives by t1 using no index, then joins
to t2 using the above index. Note: I may be reading the explain tab
incorrectly, but it lists t1 first then t2, so I think it's driving by t1.
And this even if I GROUP BY t1.TicketNo, t1.evaluation desc, t1.Username,
t1.id, or add an ORDER BY t1.TicketNo ..., or reverse the table list
(selecting from t2 first then t1).
Better yet, the engine realizes that the join to t1 selects the same row
(kind of) and so avoids the join, and instead just applies the where
Username = ? clause to determine whether to show this row or move on to the
next one.
Of course, in order to quickly find the maximum evaluation for each ticket
no, we'd like a special index: one way is that the index on ( TicketNo,
.... ) stores the TicketNo, followed by the number of records with this
TicketNo, followed by this many pointers to the original records. The
advantage of this data structure is that we can quickly find the maximum
evaluation for the first ticket no (it's the first record), then quickly
jump to the next ticket no (because we know the number of records to skip).
But I don't know what kind of indexes MySql uses, and create index does not
allow us to specify the type of index.
Note that the IN method (possible on version 4.1 and later) also drives by
no index on the outer table, then the index ( TicketNo, evaluation desc,
Username ) on t2. Again, this is assuming I'm reading the explain tab
correctly, which lists t1 first. Here goes:
select t1.id,t1.Ticket No,t1.evaluatio n,t1.Username
from A t1 where t1.evaluation = (
select max(evaluation)
from A t2
where t1.ticketno = t2.ticketno
)
and Username = 'Fergus'
;
We could try an index ( Username, TicketNo, evaluation desc ). It seems the
engine drives by t1, but only uses the first column of the index.
The select from view method
select t.id,t.TicketNo ,t.evaluation,t .Username
from A t,
(select t2.ticketno as ticketno, max(evaluation) as evaluation
from A t2 group by t2.ticketno) as tt
where
t.ticketno = tt.ticketno and t.evaluation = tt.evaluation
and t.Username = 'Fergus'
;
appears to drive by oops, what? The explain tab has me confused.
Finally, you could try the force index and related extension of MySql to try
to force a certain driving table and index. I haven't tried this though.
And you can explicitly do the inner query first by using temporary tables.
create temporary table A2 (
ticketno int,
evaluation double
);
insert into A2 (ticketno, evaluation)
select ticketno, max(evaluation)
from A
group by ticketno;
create index A2_index on A2 ( ticketno, evaluation );
select A.id,A.TicketNo ,A.evaluation,A .Username
from A inner join A2 on A.ticketno = A2.ticketno and A.evaluation =
A2.evaluation
where A.Username = 'Fergus'
;
