Hi everyone, I have monthly data from 4 years, this means 48 MyISAM tables with identical structure, all defined and indexed properly (I guess) with 650,000 rows (approx.) and 99 columns. The tables are stored in a dedicated partition with 80 Gb of free space. The server is running Windows 2000 with 696 megs of RAM, pentium 4 processor and a 7200 rpm hard disk. Mysql version used is 4.0.17
Now from my database, I have the following: mysql> show fields from fun199801; <---- One of the 48 tables. +-----------+---------------------------------------------------+------+---- -+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------------------------------------+------+---- -+---------+-------+ | id | int(10) unsigned | | MUL | 0 | | | comn | int(11) | | MUL | 0 | | | actc | int(11) | | MUL | 0 | | | ind | int(10) unsigned | | MUL | 0 | | | origin | char(1) | YES | | NULL | | | period | varchar(6) | YES | | NULL | | | pres | varchar(8) | YES | | NULL | | | c20 | bigint(20) unsigned | YES | | NULL | | | c27 | bigint(20) unsigned | YES | | NULL | | | c28 | bigint(20) unsigned | YES | | NULL | | | c30 | bigint(20) unsigned | YES | | NULL | | | c31 | bigint(20) unsigned | YES | | NULL | | | c32 | bigint(20) unsigned | YES | | NULL | | | c33 | bigint(20) unsigned | YES | | NULL | | | c39 | bigint(20) unsigned | YES | | NULL | | | c41 | bigint(20) unsigned | YES | | NULL | | | c42 | bigint(20) unsigned | YES | | NULL | | ... 99 rows in set (0.02 sec) I made a MERGE table for each year, so I have 4 MERGE tables named fx1998,fx1999,fx2000 and fx2001 Then I wrote the following query: SELECT IFNULL(fx1998.comn,0) as idcomn, IFNULL(fx1998pt.actc,0) as idactc, IFNULL( (CASE WHEN fx1998.id=stat.id THEN 5 WHEN fx1998.id=soc1998.id THEN 10 ELSE ( CASE WHEN fx1998.id BETWEEN 1 AND 49999999 THEN 1 WHEN fx1998.id BETWEEN 50000000 AND 52999999 THEN 2 WHEN fx1998.id BETWEEN 53000000 AND 58999999 THEN 4 WHEN fx1998.id BETWEEN 59000000 AND 59999999 THEN 3 WHEN (fx1998.id BETWEEN 70000000 AND 76999999) OR (fx1998.id BETWEEN 79000000 AND 79499999) THEN 7 WHEN (fx1998.id BETWEEN 77000000 AND 78999999) OR (fx1998.id BETWEEN 79500000 AND 86999999) OR (fx1998.id BETWEEN 87500000 AND 89999999) THEN 8 WHEN fx1998.id BETWEEN 87000000 AND 87499999 THEN 9 WHEN fx1998.id>=90000000 THEN 11 ELSE 6 END) END),0) as idsoc, COUNT(DISTINCT fx1998.id) as num, SUM(c108+c111+c112+c154) as sales, SUM(c109) as cost, SUM(c39+c42) as retenc, 1998 as year FROM ipt.fx1998 LEFT JOIN utils.soc1998 ON fx1998.id=soc1998.id LEFT JOIN utils.stat ON fx1998.id=stat.id GROUP BY idcomn,idactc,idsoc UNION ALL (the same syntax as the above, but for the remaining 3 years) Here is the output of the EXPLAIN command for the query: +--------------------+------+---------------+------+---------+-------------- --+---------+---------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------------+------+---------------+------+---------+-------------- --+---------+---------------------------------+ | fx1998 | ALL | NULL | NULL | NULL | NULL | 8079209 | Using temporary; Using filesort | | soc1998 | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | stat | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | fx1999 | ALL | NULL | NULL | NULL | NULL | 8222017 | Using temporary; Using filesort | | soc1998 | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | stat | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | fx2000 | ALL | NULL | NULL | NULL | NULL | 8390580 | Using temporary; Using filesort | | soc1998 | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | stat | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | fx2001 | ALL | NULL | NULL | NULL | NULL | 8462354 | Using temporary; Using filesort | | soc1998 | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | stat | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | +--------------------+------+---------------+------+---------+-------------- --+---------+---------------------------------+ 12 rows in set (0.01 sec) I noticed that the MERGE tables were not using their indexes (why?). I ran the query and waited for more than 16 HOURS !!!, so I decided to kill it. What's wrong? Any ideas? Any help will be appreciated. Thanks. Rodrigo Abt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]