MySQL vs InfoBright optimizer battle

Posted: 2nd December 2009 by Darren Cassar in Databases, Intermediate
Tags: , , ,

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?

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

  1. justmark says:

    Hi,

    Infobright doesn’t support EXPLAIN – hence the extra processing time via the MySQL Query Path. We’ve got a fair amount documented about this within our forums and Wiki. Here’s a link to the Wiki specifically about this topic with information about turning on the query execution log. Feel free to stop by the forums – we’re very prompt with our responses!

    http://www.infobright.org/wiki/Query_Execution_Log/

    Regards,
    Mark Windrim
    VP Community Relations @ Infobright

  2. Hi Mark,

    This blog post is not about EXPLAIN in infobright … but about the fact that a query which on mysql takes ages, on infobright takes very little time (awesome):
    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)

    but when setting a variable to the result:
    mysql> SET @tot = (SELECT COUNT(ac.UID) FROM ACTIVE ac JOIN ALL a ON ac.UID=a.UID);

    ^CQuery aborted BY Ctrl+C

    the variable setting fails because it takes too long, implying that running the query went through the IB optimiser but setting a variable tot equal to the result went through the mysql optimiser thus taking ages.

    Regards,
    Darren

  3. [...] the result and setting the variable to the result field, but it’s really ugly isn’t it? Read the full article the source. Share Code databases, infobright, intermediate, mysql, optimiser, variables Launch [...]

  4. justmark says:

    Darren,

    We don’t support the use of MySQL variables within a statement. Thus using @tot will force execution by the MySQL query engine, which negates the benefits you get from the Knowledge Grid. You can disable the MySQL Query Path in the latest releases of Infobright which will save you having to wait, and ultimately abort the query.

    Hope this helps,
    Mark

  5. Do you know someone who can optimize mysql database

*