I'm redesigning some software that's been in use since 2002. I'll be working with databases that will start small and grow along the way.
In the old format, data would come to us in mega-big text files that had to be parsed and manipulated and so on with Perl to remove crap and finally produce one tab delimited file. Once that file was created, another simple program would go through and use the data in each line for an INSERT statement that would put the data in a table. This table also has an Idx field that is an auto-incrementing primary key for the table. Each night at 3:30 am, a program would run and would go through the same process for each client. I never timed it, but it could take something like 30-60 seconds per client, but timing wasn't a major issue, since it had a LONG time from then until new data would be inserted into the DB. The SELECT statements to pull the data for each client involve a number of AND and OR conditions. The first one of these would create a temporary table with its results, then another long SELECT statement would create a 2nd temporary table by filtering the data out more. This would continue for a few temporary tables until the data was filtered. Then it would be packaged up and encrypted, then sent out to the client, who has a program on his computer to read that data and print it out if desired. This has worked, but for a number of reasons, a once-a-day data pull and send won't work as well with the new design. The program on the clients' computers will be able to access a special server just for them directly. (I know the concept of a server for each human client sounds inefficient, but it actually improves operations in a number of ways.) So each server will only have to provide data for one client. The big difference is that I'd like to make it so they can access the data live, or almost live. I don't mean all the data, but the subset that meets their needs. In other words, the equivalent of what was sent to them daily in the old system. Their individual servers will still get the big tab-delimited file that will still be INSERTed in to their DB line by line. But I'd like to be able to select from the new data as it comes in, once it's been given a new number in the Idx field. Is there any way to run a row of data through SELECT queries as it is being INSERTed into a table -- or just after? The reason for doing this, instead of INSERTing all the data, then running a program is that as the database grows, pulling out the data will take longer and longer, so if there were a way to screen data as it comes in, that would make it easier to provide "instantly available" data. I also know my knowledge of MySQL is quite limited, so if this can be done in better ways, I'd be interested in hearing about them. Thank you! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
