Hello... After solving some problems with the code, now am working with the real one database. On my tests was playing with 20,000 records and worked pretty fine sorting by X or Y field and setting some 'where ...' conditions.
At this moment the database has 250,000+ records, new insertions are pretty fast, searchs are pretty fast too, but generating reports is almost impossible, the computer may hang one hour and still be working with any output. My tables are something like this: CONTACTS - about 250,000 records -------------------------------------------------------------------------- | id (INT) | contact(CHAR) | active(BOOL) | ... | -------------------------------------------------------------------------- LISTS - about 60,000 records (category for some contacts) -------------------------------------------- | id (INT) | category(CHAR) | -------------------------------------------- Contacts may have a category, although not all of them have one, and some of them may be in more than one, so it is not option to use a 'category' field inside the CONTACTS table... perhaps, intuitively could have a 'SET' type field with categories, but there are not fixed categories and may (will) grow with usage... There are two conditions that should be both acomplished: Cond 1. CONTACTS.active = 1 Select all active contacts Cond 2. LISTS.category = (or <>) mycagegory Add/remove contacts from the report that are listed in mycategory If I try a select on both tables by conditioning CONTACTS.id=LISTS.ID and LISTS.category=something the whole system becomes slow and unusable, and still do not wait enough to see how long it will take. This task will be performed once or twice a week, so it must be optimized, and don't know what should be better... 1st option: let the complex select finish (have no idea of how long will take) 2nd option: generate a temporal table with results matching Cond1, and then apply Cond2 to the temporal table. Commonly, the 'category' applies to discard contacts, more than choosing them, so I would simply remove LIST.id records from TEMPORAL table where LIST.id=TEMPORAL.id and then would have the final report. But... what should be better? a single select with complex conditions or a temporal table with multiple parses to append/discard records for every category used (one or various)? Thanks for any comment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
