Fast Left Joints
I recently switched from using a local MySQL instance to using a Xeround MySQL instance for my C # application. Thus, I noticed that the queries are much slower. I am currently running a left join to create a new table and am trying to shave a few seconds off my runtime.
I have observed the following average execution time:
- CREATE TABLE AS (SELECT): 14s
- CREATE TABLE and INSERT LEFT JOIN: 14s
- SELECT statement only: 9s
- CREATE TEMPORARY TABLE AS (SELECT): 9.2s
- CREATE VIEW and Retrieve Contents: 9.5s
I am currently running an operator CREATE TABLE AS (SELECT)
. Obviously I would like to shave off the five second premium associated with going from just the select statement to CREATE TABLE
with the select clause. Using views seemed promising, but accessing data from a view is extremely slow and doesn't justify the time savings here. Using a temporary table also seems promising, but with the fact that I make my function calls, the table is dropped before I finish accessing it. Is there any other keyword I can use to tell the MySQL engine to take the contents of the statement SELECT
and put it into a barebone table?
source to share
The 5 second delay appears to be caused by the writing of the selected records to the table. If free memory allows, try creating a table using the mechanism MEMORY
:
CREATE TABLE myTempTable ENGINE=MEMORY AS SELECT (...)
For the worst performance compared to your local server (when using similar hardware specs), I would suggest comparing the output SHOW VARIABLES
for each system, especially those variables that start with "innodb _".
source to share