3

I recently switched from using a local MySQL instance to using a Xeround MySQL instance for my C# application. As such, I've noticed that queries run much more slowly. I'm currently running a left join to create a new table and am trying to shave a few seconds off my run time.

I've observed the following average run times:

  • CREATE TABLE AS (SELECT ): 14s
  • CREATE TABLE & INSERT LEFT JOIN: 14s
  • SELECT statement only: 9s
  • CREATE TEMPORARY TABLE AS (SELECT ): 9.2s
  • CREATE VIEW & Retrieve Contents: 9.5s

Currently I am running the CREATE TABLE AS (SELECT) statement. Obviously, I would like to shave off the five second premium associated with moving from a select statement only, to a CREATE TABLE with a select statement. Using views seemed promising, but accessing data from the view is extremely slow, and doesn't justify the time savings here. Using a temporary table also seems promising, but with the way that I am making my function calls, the table will get deleted before I finish accessing it. Is there another keyword I can use to tell the MySQL engine to take the contents of a SELECT statement and put it into a barebones table?

Niels Keurentjes
  • 41,402
  • 9
  • 98
  • 136
user1120540
  • 121
  • 1
  • 8

1 Answers1

0

The 5-seconds delay seems to be induced by the writing of the selected records into your table. If free memory allows, try creating your table with the MEMORY engine:

CREATE TABLE myTempTable ENGINE=MEMORY AS SELECT (...)

As for the worse performances compared to your local server (assuming similar hardware specs), I would advise comparing the output of SHOW VARIABLES on each system, especially those variables beginning with "innodb_".

RandomSeed
  • 29,301
  • 6
  • 52
  • 87