Notes

DROP PROCEDURE IF EXISTS GET_DATA_WC;
DELIMITER $$
CREATE PROCEDURE GET_DATA_WC
(
IN postsId LONGTEXT,
IN excludedTaxons TEXT
)
BEGIN

start transaction;

SET @sql = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS temp_taxons_posts (id int(11) NOT NULL, taxon_id int(11) NOT NULL, twitter_posts_id int(11) NOT NULL, weight int(11) NOT NULL , PRIMARY KEY (id), KEY tmpfxTaxonsPosts_idx (taxon_id), KEY tmpfxPostTaxons_idx (twitter_posts_id) ) ENGINE=MEMORY AS( select id, taxon_id, twitter_posts_id, weight from taxons_posts where twitter_posts_id IN (',postsId,'))');
IF LENGTH(excludedTaxons) > 0 THEN
SET @sql = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS temp_taxons_posts (id int(11) NOT NULL, taxon_id int(11) NOT NULL, twitter_posts_id int(11) NOT NULL, weight int(11) NOT NULL , PRIMARY KEY (id), KEY tmpfxTaxonsPosts_idx (taxon_id), KEY tmpfxPostTaxons_idx (twitter_posts_id) ) ENGINE=MEMORY AS( select id, taxon_id, twitter_posts_id, weight from taxons_posts where twitter_posts_id IN (',postsId,') and taxon_id not in (',excludedTaxons,'))');
END IF;


PREPARE stmt FROM @sql;
EXECUTE stmt;

SELECT STRAIGHT_JOIN SQL_NO_CACHE tp.taxon_id, tx.value, SUM(tp.weight) as weight, count( distinct twp.target_users_id) as users
FROM temp_taxons_posts as tp USE INDEX(tmpfxTaxonsPosts_idx)
INNER JOIN taxon as tx
ON tx.taxon_family_id = 1 AND tp.taxon_id = tx.id
INNER JOIN twitter_posts as twp
ON twp.id = tp.twitter_posts_id
GROUP BY tp.taxon_id, tx.value
ORDER BY SUM(tp.weight) DESC
LIMIT 100;

DROP TEMPORARY TABLE IF EXISTS temp_taxons_posts;
DEALLOCATE PREPARE stmt;

commit;
END $$
DELIMITER ;