EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

Performance of MySQL query with dynamic field insertion

Database type:

Our data is confidential thus I am creating dummy data here. The number of records is 100 000.

Table1 Form:

+------------------+
| formID  formName |
+------------------+
|   1     student  |
|   2     teacher  |
+------------------+

Table2 Field:

+--------------------+
| fieldID  fieldName |
+--------------------+
|   1      Name      |
|   2      Location  |
+--------------------+

Table3 FormField:

+----------------------------+
|   formID fieldID Value     |
+----------------------------+
|   1      1     studentName |
|   1      2     ahmedabad   |
|   2      1     teacherName |
|   2      2     mumbai      |
+----------------------------+

My query:

select frm.formName, 
(
    select frmfld.Value from FormField frmfld 
    WHERE frmfld.fieldID = 1 AND frmfld.formID = frm.formID
) AS Name,
(
    select frmfld.Value from FormField frmfld 
    WHERE frmfld.fieldID = 2 AND frmfld.formID = frm.formID
) AS Location 
From Form frm

Here, fields may increase dynamically and for that field we are appending new subquery portion every time while creating query using codebase. As this fields grows to 100, execution time for this query grows to 7 to 8mins because of the subqueries and which is not acceptable.

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `FormField` ADD INDEX `formfield_idx_fieldid_formid` (`fieldID`,`formID`);

The optimized query:

SELECT
        frm.formName,
        (SELECT
            frmfld.Value 
        FROM
            FormField frmfld 
        WHERE
            frmfld.fieldID = 1 
            AND frmfld.formID = frm.formID) AS Name,
        (SELECT
            frmfld.Value 
        FROM
            FormField frmfld 
        WHERE
            frmfld.fieldID = 2 
            AND frmfld.formID = frm.formID) AS Location 
    FROM
        Form frm

Question 2

EverSQL Database Performance Knowledge Base

Performance of MySQL query with dynamic field insertion

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles