Hello,

I am not sure if this is OT for this list. If so, please direct me to the proper place. I am using mysql++ to retrieve rows from a table. The table has between 300K-400K rows in it. the table is not indexed. I need to retrieve all the rows and load them into memory. I am using ResUse class to receive the records from the query. See below

Query q << "select * from table";

mysqlpp::ResUse use = q.use(); while ((row = use.fetch_row())) { string col1 = (string)row.lookup_by_name("col1"); string col2 = (string)row.lookup_by_name("col2") float col3 = row.lookup_by_name("col3"); }

It takes approx 12-14sec to read thru all the records. At the same time, I have similar application that reads 400K from a flat file and parses the data prior to loading into memory and it takes 2-3s. BTW, the process of loading the records into memory is the same for both applications. In both cases I am using the same computer (processor), same disks, same file system. In both cases the request to load file does not traverse the network as both db and files are local to the system. So I am a little confused why flat file loading is so much faster. I tried to increase per-connection buffers in my.cnf, but don't seem to matter so much. Here is what I adjusted so far:

key_buffer = 128M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 64M read_buffer_size = 64M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4

has anyone experimented with something like that? can anyone explain why it takes 3/4 times longer to load data from mysql then it is from a flat file? Can anything be done to speed it up?

MySQL = 4.1.12-standard MySQL++ = 1.7.32 OS =RH Linux ES3.0