How to group by each separate word and sum value in another column for that word group?, MySQL

Language: Layout:

CREATE OR REPLACE VIEW numbers2
AS SELECT 1 n  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;

create table words(keyword varchar(30), times_phrase_searched int);
insert words
select 'open windows',     1000 union
select 'closed windows',   750 union
select 'open doors',       350 union
select 'closed doors',     250 union
select 'nice window',      100 union
select 'nice windows',     50 union
select 'ugly doors',       25;

select SUBSTRING_INDEX(SUBSTRING_INDEX(words.keyword, ' ', numbers2.n), ' ', -1) word, 
       SUM(words.times_phrase_searched) times_word_in_searches
from numbers2 
inner join words on CHAR_LENGTH(words.keyword) - CHAR_LENGTH(REPLACE(words.keyword, ' ', '')) >= numbers2.n - 1
group by word
order by sum(words.times_phrase_searched) desc;


absolute service time: 0,36 sec

How to group by each separate word and sum value in another column for...