MySQL Preacher

MySQL instance running an Infobright engine


mysql> explain SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | ac | ALL | NULL | NULL | NULL | NULL | 124426 | |
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 7594256 | |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
2 rows in set (0.01 sec)

mysql> SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a ON ac.UID=a.UID;
+---------------+
| COUNT(ac.UID) |
+---------------+
| 17466 |
+---------------+
1 row in set (0.23 sec)

mysql> set @tot = (SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a ON ac.UID=a.UID);

^CQuery aborted by Ctrl+C


Took more than 60seconds —- what the …..
Why did it take a long?


mysql> set @a=2;
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a |
+------+
| 2 |
+------+
1 row in set (0.00 sec)

mysql> set @tot = (SELECT COUNT(*) FROM ACTIVE);
Query OK, 0 rows affected (0.13 sec)

mysql> select @tot;
+--------+
| @tot |
+--------+
| 124426 |
+--------+
1 row in set (0.00 sec)

mysql> set @tot = (SELECT COUNT(ac.UID) FROM ACTIVE ac);
Query OK, 0 rows affected (0.22 sec)

mysql> select @tot;
+--------+
| @tot |
+--------+
| 124426 |
+--------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(ac.UID) FROM ACTIVE ac, ALL a;
+---------------+
| COUNT(ac.UID) |
+---------------+
| 944922897056 |
+---------------+
1 row in set (0.05 sec)

mysql> set @tot=(SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a);
^CQuery aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted


Reason? …. the query is using the MySQL optimiser rather than the IB one! Why? good question (will have to ask IB devs though).

Work around, use ac temporary table to store the result and setting the variable to the result field, but it’s really ugly isn’t it?

Databases, Intermediate infobright, MySQL, optimiser, variables