There's no mystery, you get a good(er) or (really) bad plan at basically random because there is no clear cut choice for the index to use. While compelling for the ORDER BY clause and thus avoid the sort, you non-clustered index on the datetime column is a very poor choice for this query. What would make a much better index for this query would be one on (serial_number, test_date). Even better, this would make a very good candidate for a clustered index key.
As a rule of thumb time series should be clustered by the time column, because the overwhelming majority of requests are interested in specific time ranges. If the data is also inherently partitioned on a column with low selectivity, like it seems to be the case with your serial_number, then this column should be added as the leftmost one in the clustered key definition.
For 5.5 and later it is possible to use signals:
delimiter @
create trigger checkcollision
after update on players
for each row
begin
declare dummy int default 0;
select 1 into dummy from walls where x=NEW.x and y=NEW.y;
if (dummy = 1) then
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message';
end if;
end @
delimiter ;
For 5.1 and earlier version there is no support for signal. You could try to mimic it with an forced exception, like division by zero or by referencing something that does not exist. You don't get a nice error message though:
delimiter @
create trigger checkcollision
after update on players
for each row begin
declare dummy int default 0;
select 1 into dummy from walls where x=NEW.x and y=NEW.y;
if (dummy = 1) then
select 1/0 into dummy;
end if;
end @
delimiter ;
A slightly more elegant way is to use an exit handler which is supported in 5.1, still no error message though:
create trigger checkcollision
after update on players
for each row
begin
declare dummy int default 0;
DECLARE EXIT HANDLER FOR NOT FOUND begin end;
select 1 into dummy from walls where x=NEW.x and y=NEW.y;
select 1/0 into dummy;
end @
If no wall is found an empty exit handler is invoked, otherwise the trigger continues and an deliberate error is made.
By adding a dummy table like:
create table dummy (msg varchar(100) primary key);
We can force a primary key violation by inserting the same value twice from the trigger:
delimiter @
create trigger checkcollision
after update on players
for each row begin
declare dummy int default 0;
DECLARE EXIT HANDLER FOR NOT FOUND begin end;
select 1 into dummy from walls where x=NEW.x and y=NEW.y;
insert into dummy (msg) values ('ERROR: Collision')
, ('ERROR: Collision');
end @
delimiter ;
We will get an error message like (tested in 10.0.20-MariaDB):
ERROR 1062 (23000): Duplicate entry 'ERROR: Collision' for key 'PRIMARY'
You might want to encapsulate this into a stored procedure:
create procedure my_signal (msg varchar(100))
begin
insert into dummy (msg)
values ('ERROR: Collision')
, ('ERROR: Collision');
end @
which can be called from the trigger:
create trigger checkcollision
after update on players
for each row
begin
declare dummy int default 0;
DECLARE EXIT HANDLER FOR NOT FOUND begin end;
select 1 into dummy from walls where x=NEW.x and y=NEW.y;
call my_signal('ERROR: Collision');
end @
Best Answer
Your [sub_tree] derived table is using an
ORDER BYwith noTOPoperator.Try something like the following:
Granted that you are just following a MySQL book, I should probably mention the whole "no-no" on doing old style joins on SQL Server. Use
INNER JOIN,LEFT JOIN, etc rather thanFROM a,b,c WHERE a.id =b.id.