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]
