mysql 5.6

[andrea.m86]

Hi
i've just installed mysql 5.6.
my applications, that use mydac 7.0, had a great slow-down!
others applications doesn't have any problems and mysql seems to work good.
Is there any compatibility problem with the latest mysql and your components?
Is there any upgrade that fix the issue?
Thanks
Andrea

[DemetrionQ]

Hello.

Please, try downloading the latest MyDAC build (7.6.11) and check if this problem still exists.

[andrea.m86]

Hi,
I've just upgraded to the latest version.
The problem remain!
I'm trying to connecting in direct mode but the situation is the same if i use libmysql.dll (upgraded to the latest 5.6)
please let me know what can i do.

[andrea.m86]

i've noticed that a big part of the slowdown is due to the property defaultvalue of the queries.
If it is setted to true each query takes much more time to open.
but i need this property active...

[DemetrionQ]

Hello.

To retrieve values of default fields, the following service query is executed when opening the table:

Code: Select all

SELECT cast('' AS CHAR(1)) TABLE_CATALOG
     , TABLE_SCHEMA
     , TABLE_NAME
     , COLUMN_NAME
     , ORDINAL_POSITION POSITION
     , DATA_TYPE
     , CHARACTER_MAXIMUM_LENGTH DATA_LENGTH
     , NUMERIC_PRECISION DATA_PRECISION
     , NUMERIC_SCALE DATA_SCALE
     , (
       CASE IS_NULLABLE
       WHEN 'YES' THEN
         1
       ELSE
         0
       END) NULLABLE
     , COLUMN_DEFAULT DEFAULT_VALUE
FROM
  information_schema.COLUMNS
WHERE
  TABLE_SCHEMA = 'test' /*Your Database Name*/
  AND TABLE_NAME = 'table1' /*Your Table Name*/
ORDER BY
  TABLE_SCHEMA
, TABLE_NAME
, ORDINAL_POSITION

Please execute this query singly (e.g., using the TMyQuery component) - and check the speed of the execution.

[andrea.m86]

this query is much slower on mysql 5.6.
0,016 sec on Mysql 5.5
0.670 sec on Mysql 5.6
(I use a MyIsam as storage engine)

If you use the "explain" statement the results are different from a version to another:
Mysql 5.5:
key: TABLE_SCHEMA,TABLE_NAME
Using where; Open_frm_only; Scanned 0 databases; Using filesort

Mysql 5.6:
key: null
Using where; Open_frm_only; Scanned all databases; Using filesort

[DemetrionQ]

Hello.

Unfortunately, we cannot affect this SQL query execution speed. Try testing the performance for a server based on the InnoDB engine.

[andrea.m86]

no performance difference with innodb.
The performance become again good if you remove "ORDINAL_POSITION" from the order-by clause.
Also the result of the explain statement returns to be consistent with mysql 5.5

[DemetrionQ]

Hello.

Thank you for information. We will investigate the possibility of changing this functionality in one of the next versions of MyDAC.

[andrea.m86]

I reported the problem to oracle.
They opened a bug track. (bug 16423536)

Simple queries that were using the optimization described here in 5.5 :
http://dev.mysql.com/doc/refman/5.5/en/ ... ation.html
do not use it anymore in 5.6 ...

Thanks for the help.