Возможно ли перекрестное индексирование таблиц?

Рассмотрим структуру, в которой у вас есть отношение «многие к одному» (или «один ко многим») с условием (где, упорядочить по и т. Д.) На обе таблицы. Например:

CREATE TABLE tableTwo (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    eventTime DATETIME NOT NULL,
    INDEX (eventTime)
) ENGINE=InnoDB;

CREATE TABLE tableOne (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    tableTwoId INT UNSIGNED NOT NULL,
    objectId INT UNSIGNED NOT NULL,
    INDEX (objectID),
    FOREIGN KEY (tableTwoId) REFERENCES tableTwo (id)
) ENGINE=InnoDB;

и для примера запроса:

select * from tableOne t1 
  inner join tableTwo t2 on t1.tableTwoId = t2.id
  where objectId = '..'
  order by eventTime;

Допустим, вы индексируете tableOne.objectId и tableTwo.eventTime . Если вы затем объясните приведенный выше запрос , он покажет «Использование файловой сортировки». По сути, сначала применяется индекс tableOne.objectId , но он не может применить индекс tableTwo.eventTime , потому что этот индекс предназначен для всего of tableTwo (не ограниченный набор результатов), и, следовательно, он должен выполнять сортировку вручную.

Таким образом, есть способ сделать индекс кросс-таблицы, чтобы не приходилось сортировать по файлам каждый раз, когда результаты извлечено? Что-то вроде:

create index ind_t1oi_t2et on tableOne t1 
  inner join tableTwo t2 on t1.tableTwoId = t2.id 
  (t1.objectId, t2.eventTime);

Кроме того, я изучал возможность создания представления и его индексации, но индексация для представлений не поддерживается.

Решение, к которому я склонялся, если индексирование между таблицами не выполняется ' Возможно тиражирование условных данных в одну таблицу. В данном случае это означает, что eventTime будет реплицирован в tableOne , а многостолбцовый индекс будет настроен на tableOne.objectId и tableOne.eventTime (по сути, создание индекса вручную). Однако я подумал, что сначала поищу опыт других людей, чтобы увидеть, лучший ли это способ.

Большое спасибо!

Обновление:

Вот некоторые процедуры для загрузки тестовых данных и сравнения результатов:

drop procedure if exists populate_table_two;
delimiter #
create procedure populate_table_two(IN numRows int)
begin
declare v_counter int unsigned default 0;
  while v_counter < numRows do
    insert into tableTwo (eventTime) 
    values (CURRENT_TIMESTAMP - interval 0 + floor(0 + rand()*1000) minute);
    set v_counter=v_counter+1;
  end while;
end #
delimiter ;

drop procedure if exists populate_table_one;
delimiter #
create procedure populate_table_one
   (IN numRows int, IN maxTableTwoId int, IN maxObjectId int)
begin
declare v_counter int unsigned default 0;
  while v_counter < numRows do
    insert into tableOne (tableTwoId, objectId) 
      values (floor(1 +(rand() * maxTableTwoId)), 
              floor(1 +(rand() * maxObjectId)));
    set v_counter=v_counter+1;
  end while;
end #
delimiter ;

Вы можете использовать их следующим образом для заполнения 10 000 строк в tableTwo и 20 000 строк в tableOne (со случайными ссылками на tableOne и random objectId s между 1 и 5), выполнение которого заняло у меня 26,2 и 70,77 секунды соответственно:

call populate_table_two(10000);
call populate_table_one(20000, 10000, 5);

Обновление 2 (проверенный запуск SQL):

Ниже представлен проверенный и проверенный SQL, основанный на методе запуска daniHp. Это поддерживает синхронизацию dateTime на tableOne при добавлении tableOne или обновлении tableTwo . Кроме того, этот метод также должен работать для отношений «многие ко многим», если столбцы условий копируются в объединяющую таблицу. В моем тестировании 300 000 строк в tableOne и 200 000 строк в tableTwo скорость старого запроса с аналогичными ограничениями составляла 0,12 секунды, а скорость нового запроса по-прежнему отображается как 0,00 секунды. . Таким образом, есть явное улучшение, и этот метод должен хорошо работать с миллионами строк и более.

alter table tableOne add column tableTwo_eventTime datetime;

create index ind_t1_oid_t2et on tableOne (objectId, tableTwo_eventTime);

drop TRIGGER if exists t1_copy_t2_eventTime;
delimiter #
CREATE TRIGGER t1_copy_t2_eventTime
   BEFORE INSERT ON tableOne
for each row
begin
  set NEW.tableTwo_eventTime = (select eventTime 
       from tableTwo t2
       where t2.id = NEW.tableTwoId);
end #
delimiter ;

drop TRIGGER if exists upd_t1_copy_t2_eventTime;
delimiter #
CREATE TRIGGER upd_t1_copy_t2_eventTime
   BEFORE UPDATE ON tableTwo
for each row
begin
  update tableOne 
    set tableTwo_eventTime = NEW.eventTime 
    where tableTwoId = NEW.id;
end #
delimiter ;

И обновленный запрос:

select * from tableOne t1 
  inner join tableTwo t2 on t1.tableTwoId = t2.id
  where t1.objectId = 1
  order by t1.tableTwo_eventTime desc limit 0,10;