How to QUICKLY display several million lines in txt from mariaDB (mySQL)?

Question

PHP

There is a DB containing in itself 10 million records in one table, and approximately 1.5 billion in another. And a couple of small (up to 200 records) tables. This is only for development, then there will be at least 20 times more.
Server - dedicated i7-7700k, 32GB RAM, SSD 2*256GB (we'll add more later);
View request

SELECT DISTINCT acc.email, acc.password
FROM accounts acc JOIN accounts_status st ON acc.id_account=st.account_id
WHERE st.service_id in(1,5,8,7,122,147,80,58,77,60,55,15,22,14,16,78,75) AND st.status = 1

in dbForgeStudio it takes about 0.2 seconds (however, paging is enabled). In the mysql console - nothing worthwhile comes out, and if you add LIMIT 1000000it, the output occurs in a minute. When using INTO OUTFILE- the situation is similar.
As I understand it, absolutely most of the time is taken not by the request itself, but by the output. Ultimately, this whole system should produce a txt file, several million lines is quite a normal length.
How to implement a fast (in adequate time) extraction from the database to a text file?
---UPDATED 1:47 02-09-2017
It looks like the issue is the speed of the query itself. In dbForgeStudio switched Table Results to Text Resultsand got a completely different request execution time. Now the question is how you can optimize the execution time. I think it would be better to create a new question for this.

Answer 1

Hmm, let me think... maybe because there request('image')is null? Did such an elementary thought cross your mind?

Answer 2

Are you sure that the record is slowing down, and not the request itself?
In your case, if the average length of the result is 50 bytes, then 1 million of such records is approximately 50 MB, i.e. it will be written to the SSD in 1 sec.
Do

explain SELECT DISTINCT acc.email, acc.password
FROM accounts acc JOIN accounts_status st ON acc.id_account=st.account_id
WHERE st.service_id in(1,5,8,7,122,147,80,58,77,60,55,15,22,14,16,78,75) AND st.status = 1 LIMIT 100000

Perhaps the problem is in the indexes.
Look at the indexes SHOW INDEXES FROM accounts, SHOW INDEXES FROM accounts_status
Look at what you have innodb_buffer_pool_sizein your mysql configs, it should be 22400M for about 32 GB.