ALTER TABLE olemid_lausesonad ADD lause_kood VARCHAR(50);
UPDATE olemid_lausesonad SET lause_kood=substring_index(lausesona_kood, '_', 2);
ALTER TABLE olemid_lausesonad ADD INDEX (lause_kood);


SELECT substring_index(lausesona_kood, '_', 2) from olemid_lausesonad limit 5;

SELECT substring_index(lausesona_kood, '_', 2) from olemid_lausesonad limit 5;

SELECT o2.olemi_kood, COUNT(*) from olemid_lausesonad o1 join olemid_lausesonad o2 where o1.lause_kood=o2.lause_kood and o1.olemi_kood='nursipalu_harjutusväli_loc'
  group by o2.olemi_kood; 
  
SELECT o2.olemi_kood, COUNT(*) from olemid_lausesonad o1 join olemid_lausesonad o2 where o1.lause_kood=o2.lause_kood and o1.olemi_kood='jaak_joala_per'
  group by o2.olemi_kood ORDER BY  count(*) desc;  
  
  
ALTER TABLE olemid_lausesonad ADD teksti_kood VARCHAR(50);
UPDATE olemid_lausesonad SET teksti_kood=substring_index(lausesona_kood, '_', 1);
ALTER TABLE olemid_lausesonad ADD INDEX (teksti_kood);


SELECT o2.olemi_kood, COUNT(*) from olemid_lausesonad o1 join olemid_lausesonad o2 where o1.teksti_kood=o2.teksti_kood and o1.olemi_kood='jaak_joala_per'
  group by o2.olemi_kood ORDER BY  count(*) desc LIMIT 50;  
  
SELECT COUNT(*) FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.lausesona_kood=lausesonad.kood WHERE  olemid_lausesonad.olemi_kood='nursipalu_harjutusväli_loc';

SELECT COUNT(*) FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.teksti_kood=lausesonad.teksti_kood WHERE  olemid_lausesonad.olemi_kood='nursipalu_harjutusväli_loc';

SELECT lemma, COUNT(*) FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.teksti_kood=lausesonad.teksti_kood WHERE  olemid_lausesonad.olemi_kood='nursipalu_harjutusväli_loc' ORDER BY COUNT(*) DESC;


SELECT lemma, COUNT(*) FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.teksti_kood=lausesonad.teksti_kood WHERE  olemid_lausesonad.olemi_kood='nursipalu_harjutusväli_loc' AND lemma='inimene' GROUP BY lemma ORDER BY COUNT(*) DESC LIMIT 50;


SELECT lemma, COUNT(*) FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.teksti_kood=lausesonad.teksti_kood  AND 
  olemid_lausesonad.sent_id=mid(olemid_lausesonad.teksti_kood, substring_index(olemid_lausesonad.teksti_kood, '_', 1), substring_index(olemid_lausesonad.teksti_kood, '_', 2), substring_index(olemid_lausesonad.teksti_kood, '_', 1)) WHERE  olemid_lausesonad.olemi_kood='nursipalu_harjutusväli_loc' AND lemma='inimene' GROUP BY lemma ORDER BY COUNT(*) DESC LIMIT 50;
  
  
SELECT teksti_kood,  
    mid(olemid_lausesonad.teksti_kood, substring_index(olemid_lausesonad.teksti_kood, '_', 1), 
	   substring_index(olemid_lausesonad.teksti_kood, '_', 2) - substring_index(olemid_lausesonad.teksti_kood, '_', 1)) FROM olemid_lausesonad LIMIT 5;

SELECT teksti_kood,  substring_index(olemid_lausesonad.teksti_kood, '_', 1)  FROM olemid_lausesonad LIMIT 5;



SELECT lemma, COUNT(*) FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.teksti_kood=lausesonad.teksti_kood 
 WHERE  olemid_lausesonad.olemi_kood='nursipalu_harjutusväli_loc' AND 
  lausesonad.sent_id=mid(olemid_lausesonad.lausesona_kood, substring_index(olemid_lausesonad.lausesona_kood, '_', 1), substring_index(olemid_lausesonad.lausesona_kood, '_', 2)-substring_index(olemid_lausesonad.lausesona_kood, '_', 1)-1)
  GROUP BY lemma ORDER BY COUNT(*) DESC LIMIT 50;
  
  

SELECT lemma, COUNT(*) FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.teksti_kood=lausesonad.teksti_kood 
 WHERE  olemid_lausesonad.olemi_kood='nursipalu_harjutusväli_loc' AND 
  lausesonad.sent_id= substring_index(substring_index(olemid_lausesonad.lausesona_kood, '_', -2), '_', 1)
  GROUP BY lemma ORDER BY COUNT(*) DESC LIMIT 50;


SELECT mid(olemid_lausesonad.lausesona_kood, substring_index(olemid_lausesonad.lausesona_kood, '_', 1), substring_index(olemid_lausesonad.lausesona_kood, '_', 2)-substring_index(olemid_lausesonad.lausesona_kood, '_', 1)-1) as lause_id, lemma FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.teksti_kood=lausesonad.teksti_kood 
 WHERE  olemid_lausesonad.olemi_kood='nursipalu_harjutusväli_loc' 
  LIMIT 50;
  

SELECT  substring_index(substring_index(olemid_lausesonad.lausesona_kood, '_', -2), '_', 1),  substring_index(olemid_lausesonad.lausesona_kood, '_', 2),  mid(olemid_lausesonad.lausesona_kood, substring_index(olemid_lausesonad.lausesona_kood, '_', 1), substring_index(olemid_lausesonad.lausesona_kood, '_', 2)-substring_index(olemid_lausesonad.lausesona_kood, '_', 1)-1) as lause_id, lemma FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.teksti_kood=lausesonad.teksti_kood 
 WHERE  olemid_lausesonad.olemi_kood='nursipalu_harjutusväli_loc' 
  LIMIT 50;


SELECT lemma, COUNT(*) FROM olemid_lausesonad JOIN lausesonad ON olemid_lausesonad.teksti_kood=lausesonad.teksti_kood 
 WHERE  olemid_lausesonad.olemi_kood='jaak_joala_per' AND 
  lausesonad.sent_id= substring_index(substring_index(olemid_lausesonad.lausesona_kood, '_', -2), '_', 1)
  GROUP BY lemma ORDER BY COUNT(*) DESC LIMIT 50;