PHP: Filtering and export large amount of data from MySQL database -

i have large database table (more 700k records) need export .csv file. before exporting it, need check options (provided user via gui) , filter records. unfortunately filtering action cannot achieved via sql code (for example, column contains serialized data, need unserialize , check if record "passes" filtering rules.

doing records @ once leads memory limit issues, decided break process in chunks of 50k records. instead of loading 700k records @ once, i'm loading 50k records, apply filters, save .csv file, load other 50k records , go on (until reaches 700k records). in way i'm avoiding memory issue, takes around 3 minutes (this time increase if number of records increase).

is there other way of doing process (better in terms of time) without changing database structure?

thanks in advance!

the best thing 1 can php out of mix as possible. case loading csv, or exporting it.

in below, have 26 million row student table. export 200k rows of it. granted, column count small in student table. testing other things campus info students. idea hope. issue how long takes your:

... , check if record "passes" filtering rules.

which naturally occur via db engine in theory without php. without php should mantra. yet determined. point is, php processing out of equation. php many things. adequate partner in db processing not.

select count(*) students; -- 26.2 million  select * students limit 1; +----+-------+-------+ | id | thing | camid | +----+-------+-------+ |  1 |     1 |    14 | +----+-------+-------+  drop table if exists xonestoexport; create table xonestoexport (   id int not null ); insert xonestoexport (id) select id students id>1000000 limit 200000; -- 200k rows, 5.1 seconds  alter table xonestoexport add primary key(id);  -- 4.2 seconds  select s.id,s.thing,s.camid outfile 'outstudents_20160720_0100.txt'   fields terminated ',' optionally enclosed '"'   lines terminated '\r\n'   students s   join xonestoexport x   on x.id=s.id; -- 1.1 seconds 

the above 1am timestamped file 200k rows exported csv via join. took 1 second.

load data infile , select outfile companion functions that, 1 one thing, cannot beat speed short of raw table moves. secondly, people seem use latter. flexible if 1 looks can use cases , tricks.

for linux, use lines terminated '\n' ... on windows machine @ moment code blocks above. differences tend paths file, , line terminator.