Greetings, I have several solutions to a problem which requires temporary or cache tables but the different solutions are time consuming to test for performance and I was wondering if anyone had more information on this than myself.
The server is 3.23.40 without possibility for upgrade to 4.x because of the amount of extra work required for testing, deployment, etc. The simplified context is that I have several tables for individual records (REC_A, REC_B, etc.) with primary keys and one table for links between different types of records based on their primary keys (LNK). LNK is N to N so there is no constraint on the number of links a record can have to other records. I have to query these relationships in complex ways, one example being "select * from REC_A where (no links exist at all) or ((links to REC_B exist for both PKs 22 and 34) and (no links exist to REC_B for either PK in 56 or 12))". The number of records is in the hundreds of thousands and the links can get past a million. Now since this version does not support GROUP_CONCAT and having given up after a couple of days of trying to torture recursive INNER and LEFT joins to filter out records based on totally incomprehensible ON clauses, I figure I need temporary or cache intermediary tables. The solutions that I see would be to have (1) a generic "flag" table that a complex query would use to first flag the records that should get through and then join and filter with that table to return the list of records but that would mean locking and updating the table for each complex query, (2) generate an "alternate view" of the LNK table by simulating the GROUP_CONCAT function to aggregate the records in unique rows that contain the full set of links. The question for (2) is how to store the full link set in one row so that queries be able to make use of indexes. (3) would be a variation of (1) by creating a temporary table per complex query that would only serve the purposes of this query and which would contain only those record indexes that should go through so that the complex query can then perform a simple INNER JOIN to filter out the results. I've also thought about retrieving the full set of indexes for all matched records programmatically and then reinjecting this in the main query as a PK IN (...) but that would give really huge index sets that would go back and forth between the server and the client so I ruled this one out. Now unless there is another simpler and miraculous solution that I haven't thought of, I have to find out which one of these alternatives is the best one speed-wise so any help on this is sincerely appreciated. Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
