Build generative AI applications with similarity search in Cloud SQL for MySQL

Generative AI is transforming application development across industries as developers build brand new user experiences that weren’t possible before. We’re already seeing customers like Linear build amazing new AI-powered applications with Google Cloud databases. Recently, we announced that you can now also use Cloud SQL for MySQL to perform similarity searches by indexing and searching for vector embeddings generated by your favorite large language model (LLM). Cloud SQL now allows you to store vectors in the same Cloud SQL for MySQL instance you’re already using, and then search against your vector store using either an exact nearest neighbor (KNN) or approximate nearest neighbor (ANN) search.

Vector search in Cloud SQL for MySQL is built on Google’s open-source ScaNN libraries, which support multiple ANN index types: Tree-AH, Tree-SQ, and Brute Force with autotuning. Cloud SQL supports multiple distance measures, such as cosine, L2, and dot_product. Combining your vector store with your operational data allows you to create more meaningful and relevant experiences by leveraging vector search augmented with real-time data. Let’s dig in to how you can use this capability, currently in preview!

Let’s imagine you’re building a new website for a library system that helps library patrons pick out e-books that they might enjoy. Users will tell you an e-book they like, and then your website finds other e-books that they might also enjoy. You’ve just upgraded the backend of your website to Cloud SQL for MySQL’s new Enterprise Plus edition, because your library serves customers in a large city who use library services around-the-clock, and you wanted to make sure your website is taking advantage of Enterprise Plus edition’s 99.99% SLA for high availability and up to 3x higher read throughput.

The journey begins by enabling a new MySQL flag called cloudsql_vector. In order to use similarity search, you’ll need to turn your data into embeddings (vectors) and store these vectors in the catalog table. The next sections are easy-to-follow steps that guide you through:

1. Getting embeddings
2. Storing and indexing those embeddings
3. Performing similarity searches

Let’s assume that your catalog table is called library_catalog and includes details like book titles, descriptions, and copies available. You want to update your library’s catalog to include vector embeddings for all items currently in circulation. You can add a new column to store these vectors — let’s call the column item_embedding, with vector data type. To do this, you would update your table like this.

Next, generate a vector embedding for each item currently in your catalog. Use your favorite LLM for this — for example, you could use Vertex AI’s pre-trained text embeddings model to create embeddings based off of the item’s description in your catalog. The below example uses the textembedding-gecko@001 model.

Then, update that item’s row to store its new vector embedding.

Once you’ve updated all of the items in your catalog, you can add an index to your catalog to make it easy to perform similarity searches against items in the catalog. We support three different index types:

• Tree-SQ: This is the default algorithm as it requires reduced memory and supports persistence across restarts minimizing operational toil. It also features slightly better recall due to reduced compression compared to Tree-AH, but with some additional compute cost.
• Tree-AH: Ideal for applications that prioritize speed and compute efficiency at the cost of additional memory with slight operational overhead.
• Brute-Force: Suitable when the distance measure needs to be precise at the cost of speed.

You could also perform brute force searches on your vectors to get the closest nearest neighbor (KNN) by not adding an index. By default, we’ll create a Tree-SQ index.

Now, when a customer comes to the library website, they get suggestions of books similar to their favorite book, combined with a filter on what items are currently available for check out. For example, your website could run this query to get books similar to one that the customer says they like:

If you wanted to get the exact nearest items in your catalog, you could instead search for:

And this is just the start. Watch the video below to see this in action - you can follow this example, or customize for retail, healthcare, financial industries, and more. Interested in trying the preview? Get started here!