CODEWITHSUNDEEP
phpmysqlcodeigniter
Brainfeeder
Brainfeeder

Reputation: 2632

Optimization of SQL with subquery and Having

Currently we are using a custom CI library to generate PDF files from documents which exist as database records in our database.

Each document is related to the contents (== rows) with a one-has-many relation. Each row has a number (field: row_span) to indicate how many lines it will use once it gets printed in the PDF.

Per PDF page that gets build, Rows needed for that page only are selected using a subquery:

$where = $this->docType."_id = ".$this->data['doc']->id." AND visible = 1";
$sql = "SELECT *,

    (SELECT
    sum(row_span) FROM app_".$this->docType."_rows X
    WHERE X.position <= O.position 
    AND ".$where." 
    ORDER BY position ASC) 'span_total'

    FROM app_".$this->docType."_rows O 
    WHERE ".$where."
    HAVING span_total > ".(($i-1)*$this->maxRows)." AND span_total <= ".($i*$this->maxRows)." ORDER BY O.position ASC ";

$rows = $rows->query($sql);

In the code $i is the page number and $this->maxRows is loaded from the document template record which indicates how many available lines the PDF template has.

So when the SQL renders it might look like this for page 1 of an order with ID 834:

SELECT `app_order_rows`.*, 
(SELECT SUM(`app_order_rows_subquery`.`row_span`) AS row_span
    FROM `app_order_rows` `app_order_rows_subquery`
    WHERE `app_order_rows_subquery`.`position` <= 'app_order_rows.position'
    AND `app_order_rows_subquery`.`order_id` =  834
    AND `app_order_rows_subquery`.`visible` =  1
    ORDER BY `app_order_rows_subquery`.`position` asc) AS span_total 
FROM (`app_order_rows`) 
WHERE `app_order_rows`.`order_id` =  834
AND `app_order_rows`.`visible` =  1
HAVING span_total >  0
AND span_total <=  45
ORDER BY `app_order_rows`.`position` asc

And running this with EXPLAIN gives this as output:

+====+=============+=========================+======+===============+======+=========+======+======+=============================+===+
| id | select_type | table                   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |   |
+====+=============+=========================+======+===============+======+=========+======+======+=============================+===+
| 1  | PRIMARY     | app_order_rows          | ALL  | NULL          | NULL | NULL    | NULL | 1809 | Using where; Using filesort | 1 |
+----+-------------+-------------------------+------+---------------+------+---------+------+------+-----------------------------+---+
| 2  | SUBQUERY    | app_order_rows_subquery | ALL  | NULL          | NULL | NULL    | NULL | 1809 | Using where                 | 2 |
+====+=============+=========================+======+===============+======+=========+======+======+=============================+===+

This is working great, but... When we have large orders or invoices it renders the documents very slow. This might be due to the subquery.

Does anyone have an idea on how to do the same select without subquery? Maybe we will have to go for a whole new approach to select rows and build the PDF. We are open for suggestions ^^

Thanks in advance

------------------------------- edit ------------------------------

The EXPLAIN after index creation:

+====+=============+=========================+=======+===============+============+=========+=======+======+=============+===+
| id | select_type | table                   | type  | possible_keys | key        | key_len | ref   | rows | Extra       |   |
+====+=============+=========================+=======+===============+============+=========+=======+======+=============+===+
| 1  | PRIMARY     | app_order_rows          | ref   | index_main    | index_main | 5       | const | 9    | Using where | 1 |
+----+-------------+-------------------------+-------+---------------+------------+---------+-------+------+-------------+---+
| 2  | SUBQUERY    | app_order_rows_subquery | range | index_main    | index_main | 10      | NULL  | 1    | Using where | 2 |
+====+=============+=========================+=======+===============+============+=========+=======+======+=============+===+

Upvotes: 0

Views: 63

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29639

As you confirmed in the comments, the tables have no indexes.

The immediate solution would be: 

create index index_main on app_order_rows (order_id, position);

Upvotes: 1

Related Questions