Converting and optimizing queries from Oracle Database to Cloud SQL for MySQL

This document discusses the basic query differences between Oracle® andCloud SQL for MySQL, and how features in Oracle map to features inCloud SQL for MySQL. It also outlines performance considerations forCloud SQL for MySQL and ways to analyze and optimize query performance onGoogle Cloud. While this document touches on techniques to optimize storedprocedures and triggers for Cloud SQL for MySQL, it does not cover how totranslate PL/SQL code to MySQL stored procedures and functions.

When converting queries from Oracle Database to Cloud SQL for MySQL, thereare certain SQL dialect differences to consider. There are also several built-infunctions that are different or incompatible between the two database platforms.

Basic query differences

While both Oracle and Cloud SQL for MySQL support ANSI SQL, there areseveral fundamental differences when querying data, mainly around the use ofsystem functions.

The following table highlights the differences in theSELECT andFROMsyntax for Oracle and Cloud SQL for MySQL.

Oracle feature name	Oracle implementation	Cloud SQL for MySQL support	Cloud SQL for MySQL equivalent
SQL basic syntax for data retrieval	`SELECT` `FROM` `WHERE` `GROUP BY` `HAVING` `ORDER BY`	Yes	`SELECT` `FROM` `WHERE` `GROUP BY` `HAVING` `ORDER BY`
`SELECT`for output print	`SELECT 1 FROM DUAL`	Yes	`SELECT 1` or `SELECT 1 FROM DUAL`
Column aliases	`SELECT COL1 AS C1`	Yes	`SELECT COL1 AS C1` or `SELECT COL1 C1`
Table name case sensitivity	No case sensitivity(for example, table name can be`orders` and`ORDERS`)	No	Case sensitive according to the defined table name (for example table namecan be only`orders` or`ORDERS`)

Inline views

Inline views (also known as derived tables) areSELECT statements, located in theFROMclause, and used as a subquery. Inline views can help make complex queriessimpler by removing compound calculations or eliminating join operations, whilecondensing multiple separate queries into a single, simplified query.

Note: Oracle's inline views do not require the use of aliases, whileCloud SQL for MySQL inline views require a specific alias for each one.

The following example outlines a conversion example from Oracle 11g/12c toCloud SQL for MySQL for an inline view.

An inline view in Oracle 11g/12c:

SELECTFIRST_NAME,DEPARTMENT_ID,SALARY,DATE_COLFROMEMPLOYEES,(SELECTSYSDATEASDATE_COLFROMDUAL);

A working view in Cloud SQL for MySQL 5.7 with an alias:

SELECTFIRST_NAME,DEPARTMENT_ID,SALARY,DATE_COLFROMEMPLOYEES,(SELECTSYSDATE()ASDATE_COLFROMDUAL)ASA1;

Joins

Oracle's join types are supported by Cloud SQL for MySQL except for theFULL JOIN.Cloud SQL for MySQL joins support the use of alternate syntax, such as theUSING clause, theWHERE clause instead of theON clause, and theSUBQUERY in theJOIN statement.

The following table shows aJOIN conversion example.

Oracle`JOIN` type	Cloud SQL for MySQL support	Cloud SQL for MySQL`JOIN` syntax
`INNER JOIN`	Yes	`SELECT E.FIRST_NAME, D.DEPARTMENT_NAMEFROM EMPLOYEES E JOIN DEPARTMENTS DON E.DEPARTMENT_ID = D.DEPARTMENT_ID;`
`CROSS JOIN`	Yes	`SELECT E.FIRST_NAME, D.DEPARTMENT_NAMEFROM EMPLOYEES E CROSS JOIN DEPARTMENTS D`
`FULL JOIN`	No	Consider using`UNION` with`LEFT` and`RIGHT JOINS:` `SELECT E.FIRST_NAME, D.DEPARTMENT_NAMEFROM EMPLOYEES E LEFT JOIN DEPARTMENTS DON E.DEPARTMENT_ID = D.DEPARTMENT_IDUNIONSELECT E.FIRST_NAME, D.DEPARTMENT_NAMEFROM EMPLOYEES E RIGHT JOIN DEPARTMENTS DON E.DEPARTMENT_ID = D.DEPARTMENT_ID;`
`LEFT JOIN[ OUTER ]`	Yes	`SELECT E.FIRST_NAME, D.DEPARTMENT_NAMEFROM EMPLOYEES E LEFT JOIN DEPARTMENTS DON E.DEPARTMENT_ID = D.DEPARTMENT_ID;`
`RIGHT JOIN[ OUTER ]`	Yes	`SELECT E.FIRST_NAME, D.DEPARTMENT_NAMEFROM EMPLOYEES E RIGHT JOIN DEPARTMENTS DON E.DEPARTMENT_ID = D.DEPARTMENT_ID;`
`SUBQUERY`	Yes	`SELECT E.FIRST_NAME, D.DEPARTMENT_NAMEFROM EMPLOYEES E JOIN (SELECT * FROM DEPARTMENTS)DON E.DEPARTMENT_ID = D.DEPARTMENT_ID;`

While Cloud SQL for MySQL supports bothUNION andUNION ALL functions,it does not support Oracle'sINTERSECT andMINUS functions:

UNION attaches the result sets of twoSELECT statements aftereliminating duplicate records.
UNION ALL attaches the result sets of twoSELECT statements withouteliminating duplicate records.
INTERSECT returns the intersection of twoSELECT statements, only ifa record exists in the result sets from both queries.
MINUS compares two or moreSELECT statements, returning onlydistinct rows from the first query that are not returned by the second query.

Note: UseJOIN,IN,orEXISTS functions as a workaround whenconverting from the OracleINTERSECT orMINUS functions toCloud SQL for MySQL.

The following table shows some Oracle to Cloud SQL for MySQL conversionexamples.

Oracle function	Oracle implementation	Cloud SQL for MySQL support	Cloud SQL for MySQL equivalent
`UNION`	`SELECT COL1 FROM TBL1` `UNION` `SELECT COL1 FROM TBL2`	Yes	`SELECT COL1 FROM TBL1` `UNION` `SELECT COL1 FROM TBL2`
`UNION ALL`	`SELECT COL1 FROM TBL1` `UNION ALL` `SELECT COL1 FROM TBL2`	Yes	`SELECT COL1 FROM TBL1` `UNION ALL` `SELECT COL1 FROM TBL2`
`INTERSECT`	`SELECT COL1 FROM TBL1` `INTERSECT` `SELECT COL1 FROM TBL2`	No	`SELECT COL1 FROM TBL1` `WHERE COL1 IN` `(SELECT COL1 FROM TBL2)`
`MINUS`	`SELECT COL1 FROM TBL1` `MINUS` `SELECT COL1 FROM TBL2`	No	`SELECT A.COL1FROM TBL1 A LEFT JOIN TBL2 BON USING(COL1)WHERE B.COL1 IS NULL`

Scalar and group functions

Cloud SQL for MySQL provides an extensive list of scalar (single-row) andaggregation functions. Some of Cloud SQL for MySQL functions are similarto their Oracle counterparts (by name and functionality, or under a differentname but with similar functionality). While some Cloud SQL for MySQLfunctions can have identical names to their Oracle counterparts, they can alsoexhibit different functionalities.

The following table describes where Oracle and Cloud SQL for MySQLcharacter functions are equivalent by name and functionality, and where aconversion is recommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`CONCAT`	Returns the first string concatenated with the second string: `CONCAT('A', 1) = A1`	Yes	`CONCAT`	`CONCAT('A', 1) = A1`
`CONCAT USING PIPE`	`FNAME \|' '\| LNAME`	No	`CONCAT`	`CONCAT(FNAME, ' ', LNAME)`
`LOWER` or`UPPER`	Returns the string, with all letters in lowercase or in uppercase: `LOWER('SQL') = sql`	Yes	`LOWER` or`UPPER`	`LOWER('SQL') = sql`
`LPAD/RPAD`	Returns`expression1`, left or right padded tolength`n` characters with the sequence of characters in`expression2`: `LPAD('A',3,'') = *A`	Yes	`LPAD` or`RPAD`	`LPAD('A',3,'') = *A`
`SUBSTR`	Returns a portion of the string, starting at position`x` (in this case 3), with a length of`y`. The first position in the string is 1. `SUBSTR('MySQL', 3, 3)= SQL`	Yes	`SUBSTR`	`SUBSTR('MySQL', 3, 3)= SQL`
`INSTR`	Returns the position (index) of a string from a given string: `INSTR('MySQL', 'y')= 2`	Yes	`INSTR`	`INSTR('MySQL', 'y')= 2`
`REPLACE`	Returns a string with every occurrence of a search string replaced with areplacement string: `REPLACE('ORADB', 'ORA', 'MySQL')= MySQLDB`	Yes	`REPLACE`	`REPLACE('ORADB', 'ORA', 'MySQL')= MySQLDB`
`TRIM`	Trim leading or trailing characters (or both) from a string: `TRIM(both '-' FROM '-MySQL-')= MySQLTRIM(' MySQL ')= MySQL`	Yes	`TRIM`	`TRIM(both '-' FROM '-MySQL-')= MySQLTRIM(' MySQL ')= MySQL`
`LTRIM/RTRIM`	Removes from the left side or right side of a string all characters thatappear in the search: `LTRIM(' MySQL', ' ')= MySQL`	Partially	`LTRIM or RTRIM`	Oracle`LTRIM` and`RTRIM` functions take a secondparameter that specifies which leading or trailing characters to removefrom the string. Cloud SQL for MySQL functions only remove leading andtrailing whitespaces from the given string: `LTRIM(' MySQL')= MySQL`
`ASCII`	Takes a single character and returns its numeric ASCII code: `ASCII('A') = 65`	Yes	`ASCII`	`ASCII('A') = 65`
`CHR`	Returns the ASCII code value, which is a numeric value from 0 through 225,to a character: `CHR(65) = A`	Requires a different function name	`CHAR`	Cloud SQL for MySQL uses the`CHAR` function for the samefunctionality, so you need to change the function name: `CHAR(65) = A`
`LENGTH`	Returns the length of a given string: `LENGTH('MySQL') = 5`	Yes	`LENGTH`	`LENGTH('MySQL') = 5`
`REGEXP_REPLACE`	Searches a string for a regular expression pattern: `REGEXP_REPLACE('John', '[hn].', 'e') = Joe`	No	N/A	Supported as of MySQL version 8. As a workaround, use the`REPLACE` function if possible or move the logic to theapplication layer.
`REGEXP_SUBSTR`	Extends the functionality of the`SUBSTR` function by searchinga string for a regular expression pattern: `REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?')=https://console.cloud.google.com/`	No	N/A	Supported as of MySQL version 8. As a workaround, use the`SUBSTR` function if possible or move the logic to theapplication layer
`REGEXP_COUNT`	Returns the number of times a pattern occurs in a source string	No	N/A	No equivalent function available for Cloud SQL for MySQL. Move thislogic to the application layer.
`REGEXP_INSTR`	Search a string position (index) for a regular expression pattern	No	N/A	Supported as of MySQL version 8. If on an older version, move this logic tothe application layer.
`REVERSE`	Returns the reversed string for a given string: `REVERSE('MySQL')= LQSyM`	Yes	`REVERSE`	`REVERSE('MySQL')= LQSyM`

The following table describes where Oracle and Cloud SQL for MySQL numericfunctions are equivalent by name and functionality, and where a conversion isrecommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`ABS`	Returns the absolute value of a given number: `ABS(-4.6) = 4.6`	Yes	`ABS`	`ABS(-4.6) = 4.6`
`CEIL`	Returns the smallest integer that is greater than or equal to the givennumber: `CEIL(21.4) = 22`	Yes	`CEIL`	`CEIL(21.4) = 22`
`FLOOR`	Returns the largest integer equal to or less than the given number: `FLOOR(-23.7) = -24`	Yes	`FLOOR`	`FLOOR(-23.7) = -24`
`MOD`	Returns the remainder of`m` divided by`n`: `MOD(10, 3) = 1`	Yes	`MOD`	`MOD(10, 3) = 1`
`ROUND`	Returns`n` rounded to integer places to the right ofthe decimal point: `ROUND(1.39, 1) = 1.4`	Yes	`ROUND`	`ROUND(1.39, 1) = 1.4`
`TRUNC`(number)	Returns`n1` truncated to`n2`decimal places. The second parameter is optional. `TRUNC(99.999) = 99TRUNC(99.999, 0) = 99`	Requires a different function name	`TRUNCATE`(number)	Cloud SQL for MySQL function has a different name and the secondparameter is mandatory. `TRUNCATE(99.999, 0) = 99`

The following table describes where Oracle and Cloud SQL for MySQLdatetime functions are equivalent by name and functionality, and where aconversion is recommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`SYSDATE`	Returns the current date and time set for the operating system on which thedatabase server resides: `SELECT SYSDATEFROM DUAL;= 31-JUL-2019`	Yes	`SYSDATE()`	Cloud SQL for MySQL`SYSDATE()` mustinclude parentheses and returns by default a different`datetime` format than the Oracle`SYSDATE`function: `SELECT SYSDATE()FROM DUAL;= 2019-01-31 10:01:01.0` You can change the`datetime` format at the session level
`SYSTIMESTAMP`	Returns the system date, including fractional seconds and time zone: `SELECT SYSTIMESTAMP FROM DUAL= 01-JAN-19 07.37.11.622187000 AM +00:00`	Requires a different function name	`CURRENT_TIMESTAMP`	Cloud SQL for MySQL function returns a different`datetime`format by default. To reformat the output, use the`DATE_FORMAT()` function. `SELECT CURRENT_TIMESTAMPFROM DUAL;= 2019-01-31 06:55:07`
`LOCAL_TIMESTAMP`	Returns the current date and time as a`TIMESTAMP` type: `SELECT LOCALTIMESTAMPFROM DUAL= 01-JAN-19 10.01.10.123456 PM`	Returns a different`datetime` format	`LOCAL_TIMESTAMP`	Cloud SQL for MySQL function returns a different`datetime`format than the default format for Oracle. To reformat the output, use the`DATE_FORMAT()` function. `SELECT LOCAL_TIMESTAMPFROM DUAL= 2019-01-01 10:01:01.0`
`CURRENT_DATE`	Returns the current date: `SELECT CURRENT_DATEFROM DUAL= 31-JAN-19`	Returns a different`datetime` format	`CURRENT_DATE`	Cloud SQL for MySQL function returns a different`datetime`format. To reformat the output, use the`DATE_FORMAT()`function. `SELECT CURRENT_DATEFROM DUAL= 2019-01-31`
`CURRENT_TIMESTAMP`	Returns the current date and time: `SELECT CURRENT_TIMESTAMPFROM DUAL= 31-JAN-19 06.54.35.543146 AM +00:00`	Returns a different`datetime` format	`CURRENT_TIMESTAMP`	Cloud SQL for MySQL function returns a different`datetime`format. To reformat the output, use the`DATE_FORMAT()`function. `SELECT CURRENT_TIMESTAMPFROM DUAL= 2019-01-31 06:55:07`
`ADD_MONTHS`	Returns the date plus integer months: `ADD_MONTHS(SYSDATE, 1)= 31-JAN-19`	Requires a different function name	`ADDDATE`	Cloud SQL for MySQL function returns a different`datetime`format. To reformat the output, use the`DATE_FORMAT()`function. `ADDDATE(SYSDATE(), 1)= 2019-08-01 06:42:49.0`
`EXTRACT` (date part)	Returns the value of a`datetime` field based on an intervalexpression: `EXTRACT(YEAR FROM DATE '2019-01-31')= 2019`	Yes	`EXTRACT` (date part)	`EXTRACT(YEAR FROM DATE '2019-01-31')= 2019`
`LAST_DAY`	Returns the last day of the month for a given date: `LAST_DAY('01-JAN-2019')= 31-JAN-19`	Partially	`LAST_DAY`	Cloud SQL for MySQL function returns a different`datetime`format than default format for Oracle. To reformat the output, use the`DATE_FORMAT()` function. `LAST_DAY('2019-01-01')= 2019-01-31`
`MONTH_BETWEEN`	Returns the number of months between the given dates`date1` and`date2`: `MONTHS_BETWEEN(SYSDATE, SYSDATE-60)= 1.96`	Partially	`PERIOD_DIFF`	Cloud SQL for MySQL`PERIOD_DIFF` function returns thedifference in months as an integer number between two periods (formatted as`YYMM` or`YYYYMM`): `PERIOD_DIFF('201903', '201901')= 2`
`TO_CHAR (Datetime)`	Converts a number,`datetime`, or timestamp type to astring type `TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS')= 01-01-2019 10:01:01`	Requires a different function name	`DATE_FORMAT`	Cloud SQL for MySQL`DATE_FORMAT` function formats a datevalue according to a format string: `DATE_FORMAT(SYSDATE(),'%d-%m-%Y %H:%i:%s')01-01-2019 10:01:01`

The following table describes where Oracle and Cloud SQL for MySQLencoding and decoding functions are equivalent by name and functionality, andwhere a conversion is recommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`DECODE`	Compares the expression to each search value one by one using thefunctionality of an`IF-THEN-ELSE` statement	No	`CASE`	Use Cloud SQL for MySQL`CASE` statement for similarfunctionality
`DUMP`	Returns a`VARCHAR2` value containing the data type code, lengthin bytes, and internal representation of expression	No	N/A	Not supported
`ORA_HASH`	Computes a hash value for a given expression	No	`MD5 or SHA`	Use the`MD5` function for 128-bit checksums or the`SHA` function for 160-bit checksums

The following table describes where Oracle and Cloud SQL for MySQLconversion functions are equivalent by name and functionality, and where aconversion is recommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`CAST`	Converts one built-in data type or collection-typed value into anotherbuilt-in data type or collection-typed value: `CAST('1' as int) + 1= 2`	Partially	`CAST`	Adjust depending on if an explicit or implicit conversion is required: `CAST('1' AS SIGNED) + 1= 2`
`CONVERT`	Converts a character string from one-character set to another: `CONVERT ('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1')= ?? ?? ?? A B C`	Partially	`CONVERT`	Cloud SQL for MySQL`CONVERT` function requires someadjustments to the syntax and parameters: `CONVERT('Ä Ê Í A B C ' USING utf8)= Ä Ê Í A B C`
`TO_CHAR` (string/numeric)	The function converts a number or date to a string: `TO_CHAR(22.73,'$99.9')= $22.7`	No	`FORMAT`	Cloud SQL for MySQL`FORMAT` function converts a number toa format like`#,###,###.##`, rounding it to a decimal place andthen returning the result as a string: `CONCAT('$',FORMAT(22.73, 1))= $22.7`
`TO_DATE`	Oracle's`TO_DATE` function converts a string to a date based ona`datetimecode` format: `TO_DATE('2019/01/01', 'yyyy-mm-dd')= 01-JAN-2019`	Requires a different function name	`STR_TO_DATE`	Cloud SQL for MySQL`STR_TO_DATE` function takes a string andreturns a date based on a`datetime` format: `STR_TO_DATE('2019/01/01', '%Y/%m/%d')= 2019-01-01`
`TO_NUMBER`	Converts expression to a value of a`NUMBER` data type: `TO_NUMBER('01234')= 1234`	Requires a different function name	`CAST`	Use the Cloud SQL for MySQL`CAST` function to return the sameresult as the Oracle`TO_NUMBER` function: `CAST('01234' as SIGNED)= 1234`

The following table describes where Oracle and Cloud SQL for MySQLconditionalSELECT functions are equivalent by name and functionality, andwhere a conversion is recommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`CASE`	The`CASE` statement chooses from a sequence of conditions andruns a corresponding statement with the following syntax: `CASE WHEN condition THEN result[WHEN ...] [ELSE result]END`	Yes	`CASE`	In addition to the`CASE` function, Cloud SQL for MySQLsupports the use of`IF/ELSE`conditional handling inside the`SELECT` statement: `CASE WHEN condition THEN result[WHEN ...] [ELSE result]END`

The following table describes where Oracle and Cloud SQL for MySQL nullfunctions are equivalent by name and functionality, and where a conversion isrecommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`COALESCE`	Returns the first non-null expression in the expression list: `COALESCE(null, '1', 'a')= a`	Yes	`COALESCE`	`COALESCE(null, '1', 'a')= 1`
`NULLIF`	Performs a comparison between`expression1` and`expression2`. If they areequal, the function returns`null`.If they are not equal, the function returns`expression1`: `NULLIF('1', '2')= a`	Yes	`NULLIF`	`NULLIF('1', '2')= a`
`NVL`	Replaces a`null` value with a string in the results of a query: `NVL(null, 'a')= a`	No	`IFNULL`	`IFNULL(null, 'a')= a`
`NVL2`	Determines the value returned by a query based on whether anexpression is null or not null	No	`CASE`	The`CASE` statementchooses from a sequence of conditions and runs a correspondingstatement: `CASE WHEN condition THEN result[WHEN ...] [ELSE result]END`

The following table describes where Oracle and Cloud SQL for MySQLenvironment and identifier functions are equivalent by name and functionality,and where a conversion is recommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`SYS_GUID`	Generates and returns a globally unique identifier (`RAW` value)made up of 16 bytes: `SELECT SYS_GUID()FROM DUAL=8EFA4A31468B4C6DE05011AC0200009E`	No	`REPLACE` and`UUID`	As a workaround, use the`REPLACE` and`UUID` functions to simulate the`SYS_GUID`function: `REPLACE(UUID(), '-', '')`
`UID`	Returns an integer that uniquely identifies the session user (the user whois logged on): `SELECT UID FROM DUAL= 43`	No	N/A	N/A
`USER`	Returns the username of the user who is connected to the currentsession: `SELECT USER FROM DUAL=username`	Yes	`USER + INSTR + SUBSTR`	The Cloud SQL for MySQL`USER` function returns theusername and host name (`root@IP_ADDRESS`) for theconnection. To retrieve only the username, use the additional supportingfunctions: `SELECTSUBSTR(USER(), 1, INSTR(USER(), '@') -1) FROM DUAL= root`
`USERENV`	Returns information about the current Oracle session, such as the languageof the session: `SELECT USERENV('LANGUAGE')FROM DUAL= ENGLISH_AMERICA.AL32UTF8`	No	`SHOW SESSIONVARIABLES`	Cloud SQL for MySQL`SHOW SESSION VARIABLES` statementreturns the settings for the current session: `SHOW SESSION VARIABLES LIKE '%collation%';= utf8_general_ci`
`ROWID`	Oracle assigns each row of a table a unique`ROWID` to identifythe row in the table. The`ROWID` is the address of the rowthat contains the data object number, the data block of the row, the rowposition, and the data file.	Partially	N/A	`ROW_NUMBER()` is available starting in MySQL8.0. If you're using an earlier version, emulate the same functionality by usinga session variable`@row_number`.
`ROWNUM`	Returns a number that represents the order that a row is returned by anOracle table	Partially	N/A	`ROW_NUMBER()` is available starting in MySQL 8.0. If you'reusing an earlier version, emulate the same functionality by using asession variable`@row_number`.

The following table describes where Oracle and Cloud SQL for MySQLaggregate (group) functions are equivalent by name and functionality, and wherea conversion is recommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`AVG`	Returns the average value of a column or expression	Yes	`AVG`	Equivalent to Oracle
`COUNT`	Returns the number of rows returned by a query	Yes	`COUNT`	Equivalent to Oracle
`COUNT(DISTINCT)`	Returns the number of unique values in the column or expression	Yes	`COUNT(DISTINCT)`	Equivalent to Oracle
`MAX`	Returns the maximum value of a column or expression	Yes	MAX	Equivalent to Oracle
`MIN`	Returns the minimum value of a column or expression	Yes	`MIN`	Equivalent to Oracle
`SUM`	Returns the sum of a value of a column or expression	Yes	`SUM`	Equivalent to Oracle
`LISTAGG`	Orders the data within each group specified in the`ORDER BY`clause and concatenates the values of the measure column: `SELECT LISTAGG(DEPARTMENT_NAME, ', ')WITHIN GROUP(ORDER BY DEPARTMENT_NAME) DEPTFROM DEPARTMENTS;-- Single line results= Accounting, Administration, Benefits, Construction`	Requires a different function name and syntax	`GROUP_CONCAT`	Use Cloud SQL for MySQL`GROUP_CONCAT` function to returnequivalent results: `SELECT GROUP_CONCAT(DEPARTMENT_NAME ORDER BY DEPARTMENT_NAME SEPARATOR ', ') DEPTFROM DEPARTMENTS;-- Single line results= Accounting, Administration, Benefits, Construction`

The following table describes where Oracle and Cloud SQL for MySQLFETCHfunction is equivalent by name and functionality.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function	Cloud SQL for MySQL implementation
`FETCH`	Retrieves a specified number of rows from the result set of a multi-rowquery: `SELECT * FROMEMPLOYEESFETCH FIRST 10 ROWS ONLY;`	Yes	`LIMIT`	Use the MySQL`LIMIT`clause to retrieve rows from a query: `SELECT * FROMEMPLOYEESLIMIT 10;`

Basic filtering, operators, and subqueries

Basic filtering, operator functions, and subqueries are relativelystraightforward to convert, with nominal effort required. Most of the effortrevolves around converting date formats because Oracle andCloud SQL for MySQL use different default date formats:

The OracleSYSDATE function returns this format by default:01-AUG-19.
The Cloud SQL for MySQLSYSDATE() function returns this format bydefault:2019-08-01 12:04:05.

To set date and time formats, use the MySQL DATE_FORMAT or theSTR_TO_DATE functions.

The following table describes where Oracle and Cloud SQL for MySQL basicfiltering, operators, and subquery functions are equivalent by name andfunctionality, and where a conversion is recommended.

Oracle function	Oracle implementation	Cloud SQL for MySQL equivalent	Cloud SQL for MySQL function
`EXISTS/NOT EXISTS`	Yes	`EXISTS/NOT EXISTS`	`SELECT * FROM DEPARTMENTS DWHERE EXISTS (SELECT 1 FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID);`
`IN/NOT IN`	Yes	`IN/NOT IN`	`SELECT * FROM DEPARTMENTS DWHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES E);-- ORSELECT * FROM EMPLOYEESWHERE (EMPLOYEE_ID, DEPARTMENT_ID)IN((100, 90));`
`LIKE/NOT LIKE`	Yes	`LIKE/NOT LIKE`	`SELECT * FROM EMPLOYEESWHERE FIRST_NAME LIKE '_e_n%';`
`BETWEEN/NOT BETWEEN`	Yes	`BETWEEN/NOT BETWEEN`	`SELECT * FROM EMPLOYEESWHERE EXTRACT(YEAR FROM HIRE_DATE)NOT BETWEEN 2001 and 2004;`
`AND/OR`	Yes	`AND/OR`	`SELECT * FROM EMPLOYEESWHERE DEPARTMENT_ID IN(100, 101)AND (SALARY >= 1000 OR HIRE_DATE <= '2006-02-05');`
`SubQuery`	Yes	`SubQuery`	Cloud SQL for MySQL supports subqueries in the`SELECT`clause, in the`JOIN` clause, and for filtering in the`WHERE/AND` clauses: -- SELECT SubquerySELECT D.DEPARTMENT_NAME, (SELECT AVG(SALARY) AS AVG_SAL FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID) AVG_SALFROM DEPARTMENTS D;-- JOIN SubquerySELECT FIRST_NAME, LAST_NAME, SALARYFROM EMPLOYEES E JOIN (SELECT * FROM DEPARTMENTS WHERE LOCATION_ID = 2700) DON E.DEPARTMENT_ID = D.DEPARTMENT_ID;-- Filtering SubquerySELECT FIRST_NAME, LAST_NAME, SALARYFROM EMPLOYEESWHERE SALARY < (SELECT AVG(SALARY) FROM EMPLOYEES);
Operators	Yes	Operators	Cloud SQL for MySQL supports all basic operators: `> \| >= \| < \| <= \| = \| <> \| !=`

Best practices for Cloud SQL for MySQL queries

To maintain comparable levels of performance between Cloud SQL for MySQLand Oracle, you might need to optimize your queries. These optimizations includechanging the index structures and adjusting the database schema. This sectionprovides a few guidelines to help you achieve comparable query performance onCloud SQL for MySQL.

Create a clustered index

When using theInnoDB storage engine,a best practice is to define a table with a primary key, because this keycreates a clustered index on that table. In addition to improving queryperformance, this approach also lets you create additional secondaryindexes. You want to avoid creating too many indexes, though. Having redundantindexes does not improve performance and can slow downDML execution. This best practice leads to a second best practice: regularly monitorfor redundant indexes, and if you have any redundant ones, drop them from thedatabase.

Use the following query to identify tables with no primary keys so that you cancreate primary keys for them:

mysql>SELECTt.table_schema,t.table_nameFROMinformation_schema.tablestLEFTJOINinformation_schema.statisticssONt.table_schema=s.table_schemaANDt.table_name=s.table_nameANDs.non_unique=0WHEREs.table_nameISNULLANDt.table_schemaNOTIN('sys','information_schema','mysql','performance_schema')ANDt.`TABLE_TYPE` <>'VIEW';

Use the following query to find tables which have no indexes so you can createindexes for them:

mysql>SELECTt.table_schema,t.table_nameFROMINFORMATION_SCHEMA.tablestWHEREtable_nameNOTIN(SELECTtable_nameFROM(SELECTtable_name,index_nameFROMinformation_schema.statisticsGROUPBYtable_name,index_name)tab_ind_colsGROUPBYtable_name)ANDtable_schemaNOTIN('sys','information_schema','mysql','performance_schema')ANDTABLE_TYPE <>'VIEW';

Use the following query to check for redundant indexes so that you can removethe redundancies:

mysql>SELECT*FROMsys.schema_redundant_indexes;

Adjust query parameters

To tune query performance, you might need to adjust session parameters.Cloud SQL for MySQL has a set offlags that you can alter for this purpose, including the following flags:

InnoDB-related parameters
SORT parameters
JOIN parameters
Cache-handling parameters

Monitor queries

Slow-running queries can cause the system to stop responding or lead to otherbottlenecks, so it's important to regularly monitor queries.

There are several ways to diagnose slow-running SQL statements:

Use theCloud SQL for MySQL dashboard for real-time and historical insights on slow-running queries.
UseCloud Monitoring to monitor the Cloud SQL for MySQL slow query log.
Use the Cloud SQL for MySQLstatement_analysis view to see theruntime statistics about a SQL statement:
```
mysql>SELECT*FROMsys.statement_analysis;
```

Analyze Cloud SQL for MySQL queries

The query optimizer in Cloud SQL for MySQL generates an execution plan forSELECT,INSERT,UPDATE, andDELETE statements. These plans are usefulwhen you adjust a slow-running query. There are a few considerations to keep inmind:

Execution plans are not database objects that need to be migrated;rather, they are a tool for analyzing performance differences betweenOracle and Cloud SQL for MySQL running the same statement on identicaldatasets.
Cloud SQL for MySQL does not support the same execution plansyntax, functionality, or output as Oracle.

Here is an example plan to illustrate the differences between an Oracleexecution plan and a Cloud SQL for MySQL execution plan:

SQL>EXPLAINPLANFORSELECT*FROMEMPLOYEESWHEREEMPLOYEE_ID=105;SQL>SELECT*FROMTABLE(DBMS_XPLAN.DISPLAY(FORMAT=>'ALL +OUTLINE'));Planhashvalue:1833546154---------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|69|1(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|EMPLOYEES|1|69|1(0)|00:00:01||*2|INDEXUNIQUESCAN|EMP_EMP_ID_PK|1||0(0)|00:00:01|---------------------------------------------------------------------------------------------mysql>EXPLAINSELECT*FROMEMPLOYEESWHEREEMPLOYEE_ID=105;+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+|1|SIMPLE|EMPLOYEES|NULL|const|PRIMARY|PRIMARY|3|const|1|100.00|NULL|+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

Optimize stored procedures and triggers

In contrast to Oracle, Cloud SQL for MySQL stored procedures andfunctions are parsed at each execution. A helpful tool for benchmarking storedprocedure and function performance is theMySQLBENCHMARK() utility.This tool takes two parameters, an iteration count and an expression, andestimates the runtime of the given expression (for example, stored procedure,function, andSELECT statement). The output represents the approximate totalruntime over all iterations.

The following is an example to illustrate theBENCHMARK() utility:

-- SELECT Expression Examplemysql>selectbenchmark(10000000,'select sysdate()');+-----------------------------------------+|benchmark(10000000,'select sysdate()')|+-----------------------------------------+|0|+-----------------------------------------+1rowinset(0.12sec)-- Result: Run time of 0.12 sec for 1,0000,000 iterations-- FUNCTION Examplemysql>selectbenchmark(1000000,func1());+-----------------------------+|benchmark(1000000,func1())|+-----------------------------+|0|+-----------------------------+1rowinset(2.54sec)-- Result: Run time of 2.54 sec for 1,000,000 iterations

If you notice a performance regression during the conversion, use theMySQLEXPLAINcommand to identify possible factors contributing to the regression. One commonsolution for slow performance is to alter a table index structure to accommodatethe MySQL optimizer. Another common practice is to optimize a converted PL/SQLcode by reducing unnecessary data retrieval or by using temporary tables withinthe procedural MySQL code.

What's next

Explore more aboutMySQL user accounts.
Explore reference architectures, diagrams, and best practices about Google Cloud.Take a look at ourCloud Architecture Center.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-11-24 UTC.

Movatterモバイル変換