Two very big mysql tables, should I merge them in one or have a UNION query?

Asked Oct 08 '16 at 17:49

Active Oct 08 '16 at 22:54

Viewed 47 times

I've got two very big tables, one with 5M rows the other with 37M rows. The first one has all the descriptive text fields filled for each row with several fulltext indexes (size 33GB). The later one doesn't have content for those text fields yet and I'm about to add text to those (size 20GB).

I've already asked about optimizing my search query on the 5M-row table on here and it seems it cannot be optimized further:

Optimizing a SELECT … UNION … query with ORDER and LIMIT on a table with 5M+ rows

We've managed reducing the execution time on the above query 10 times by making the server more powerful. But now if I add another SELECT ... UNION ... to the above mentioned query for the 37M-row table, its execution time exceeds more than 10 seconds which is not acceptable anymore.

So here is the question, would it be theoretically better if I move rows from the 37M-row table to the other one when adding additional text contents to the rows or that won't change anything?

edited May 23 '17 at 12:26

Community

asked Oct 08 '16 at 17:49

SAVAFA

A join is the best practice, maybe local caching also. – Koen Hollander Oct 08 '16 at 17:53
Have you ever thought about using something like https://www.elastic.co/de/products/elasticsearch? We have a database with about 100M rows and improved search time(fulltext search etc) from 10s to about 0.2s. If you're using any modern framework like laravel it's really easy to integrate. – Evo_x Oct 08 '16 at 19:05
Have you tested it in InnoDB? The limits there are doubtless different. – Rick James Oct 08 '16 at 21:07
@KoenHollander, what do you mean by local cashing? – SAVAFA Oct 08 '16 at 22:01
@Evo_x I'm using regular MySQL and not familiar with others. Is your solution suitable for my case? – SAVAFA Oct 08 '16 at 22:03
@RickJames, yes both tables are in InnoDB now. – SAVAFA Oct 08 '16 at 22:03
You can create a local JSON file, to reduce data – Koen Hollander Oct 09 '16 at 11:38

Two very big mysql tables, should I merge them in one or have a UNION query?

0 Answers0