I am designing a scraping system that will scrape large set of data from websites. The system is expected to collect data from websites and store it to database.
I am storing structured data parsed from HTML tags.
For example: If it's scraping a hotel listing, the data would contain the hotel id, hotel name, location, price, reviews, ratings, URL, etc.
This database is expected to have over 20-50 million records. The data set can contain information in different languages. The data will be populated periodically (more or less a million every week).
The database needs to be optimized for filtering and search. Also it should be capable of managing the other language characters (for example Chinese). I need fast execution and retrieval of data along with character support.
I was considering using MySQL, but I don't know if there are any better options. If MySQL is the best option, which storage engine, collation, and charset should be used?
I have a DB design plan to store data for each week store in separate tables, which could be later combined (if needed) as per the requirements.
Does Chinese characters cause any troubles while JOINing the queries?
Does storing the data in multiple tables create any performance issue while retrieval. (I planned of multiple tables since an indexed big table could make data insertion slower)