Hello! I have a problem that someone here may have solved. First of all, I want to try an WITHOUT USING ElasticSearch solution or something.
Come on: I have a bank with approximately 40 tables that have the same columns. One of these columns is called "content" and it is of type TEXT. Some tables have up to 80Gb of data and 7 million rows. The average is approximately 700,000 rows per table. Here's the problem: I do a search using MATCH (content) AGAINST ("+ shirt + yellow" IN BOOLEAN MODE) in each of the tables I want to find that term and then used a UNION ALL to group the results.
Considering only two tables, for easy typing here, it would look like this:
SELECT id, nome, idade FROM ((SELECT id, nome, idade FROM tabela_a WHERE MATCH(conteudo) AGAINST("+camisa +amarela" IN BOOLEAN MODE)) UNION ALL (SELECT id, nome, idade FROM tabela_b WHERE MATCH(conteudo) AGAINST("+camisa +amarela" IN BOOLEAN MODE))) LIMIT 0, 10
Then, when I make this query in a restricted environment (using 40 tables with 20 thousand rows each), the query is ready in up to 3 seconds. It's still not the best thing in the world, but OK.
When I make this query in the production environment, I drop the server. The query time is almost infinite. Some queries return after many minutes, and that makes things unfeasible.
This was clearly not the best approach since, considering this analysis , even in 40 tables, time would be measured, still, in seconds, not in minutes.
How did you solve this problem? If they did not go through this, how could they think of an exit?
Thank you all!