Re: [google-cloud-sql-discuss] Foreign Key Indexes not getting used during query execution

Hi Dave,

Cloud SQL runs essentially standard MySQL, with the same query planner and optimizer. For the same version, config, schema, query and data (they optimizer uses estimates of the size indexes to make decisions) I would expect the same query plan. If they query plan differs, I expect one of these things is different to cause it. Perhaps look to see:

• Check the MySQL version of your Cloud SQL instance and other MySQL instance. You can do this by with SHOW VARIABLES LIKE 'version'; If the versions differ their might be changes to the optimizer. You might be able to find the exact change by looking through the MySQL release log.
  
• Check the table schemas, especially the indexes. Even a small schema change like NOT NULL vs NULL can radically change a query plan by making an index unusable for a certain query.
• Check that the queries you are comparing are identical.
• Check that the data on the two instance is identical. Also try the comparison directly after an ANAYZE TABLE statement. MySQL keeps statistics about the keys of your indexes and uses this when choosing between possible query plans. If the data or estimates about the data differ MySQL may choose a different query plan.
• Check that your MySQL instance has the same flags as your Cloud SQL instance. Some flags can cause different behavior of the query planner.

David

On Mon, Dec 7, 2015 at 10:36 AM, Dave Greenly <dgreenepic@gmail.com> wrote:

I have a situation where i built some foreign keys in my tables, and executed an explain plan on some of my "uglier" queries. By ugly, I mean they have nested sub queries, and we are starting to normalize our database.

When I run the queries on my local MySQL DB, the explain plan shows a nice performance increase, as well as the correct FK's getting used.

When I set the same thing up on the Google Cloud MySQL DB, the explain plan does not show the execution using any of the FK's?

Does anyone have any idea of why the MySQL instances would be acting differently like that? The DB is the same version, I ran the optimize command on the tables with no luck?

Thanks Dave

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/61d95407-0c68-46d0-ac4f-7caf6e8ee394%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/CAJZK_bbTcqgP0kh0DOzQ0wknO%3DgXpVhDyAajqYwpj%3DQd%2BipUoA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.