I think there are some required indexes missing in your table structure. If you showed us your real SHOW CREATE TABLE CONTACTS \G and SHOW CREATE TABLE LISTS \G as well as text of SELECTs that kill your mysqld, you'd get better replies. 250k/60k records is not that huge tables really.
On 9/25/05, Miguel Cardenas <[EMAIL PROTECTED]> wrote: > 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] > > -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
