Re: Subset Query Speed Help from .46 second to 7 minutes!

[Brian Pilati]

Felix Geerinckx wrote:

Felix, thank you for your time and patience. I am definately on the learning curve here.

For further background. I am running a mfc c++ program that uses myodbc. I have been calling the two scripts using a method which looks like: Find the company, find the invoice starting balance, find the payments, subtract the balance from the payments, display the number. As time progresses the method has become slower because of the increased interations looping through invoices. I wanted to combine the two and since I moved from mysql 3 to mysql 4, I figured I could.

> The SHOW INDEX command gives extra info. Please
> post the output of the command.

** Table Name Location **

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| location | 0 | PRIMARY | 1 | id | A | 8844 | NULL | NULL | | BTREE | |
| location | 1 | index1 | 1 | company_id | A | 8844 | NULL | NULL | YES | BTREE | |
| location | 1 | index1 | 2 | country | A | 8844 | NULL | NULL | YES | BTREE | |
| location | 1 | index1 | 3 | port_id | A | 8844 | NULL | NULL | YES | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

** Table Name company **

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| company | 0 | PRIMARY | 1 | id | A | 8981 | NULL | NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

** Table Name Countries **

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| countries | 0 | PRIMARY | 1 | id | A | 221 | NULL | NULL | | BTREE | |
| countries | 1 | index1 | 1 | region | A | 1 | NULL | NULL | YES | BTREE | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

** Table Name Location_Connection **

---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| location_connection | 0 | PRIMARY | 1 | id | A | 17801 | NULL | NULL | | BTREE | |
| location_connection | 1 | index1 | 1 | invoice_id | A | 8900 | NULL | NULL | YES | BTREE | |
| location_connection | 1 | index1 | 2 | location_id | A | 17801 | NULL | NULL | YES | BTREE | |
| location_connection | 1 | index1 | 3 | contact_id | A | 17801 | NULL | NULL | YES | BTREE | |
+---------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

** Table Name Invoices **

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| invoices | 0 | PRIMARY | 1 | id | A | 9352 | NULL | NULL | | BTREE | |
| invoices | 1 | index1 | 1 | deal_id | A | 850 | NULL | NULL | YES | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

** Table Name General_ledger **

+----------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| general_ledger | 0 | PRIMARY | 1 | id | A | 50647 | NULL | NULL | | BTREE | |
| general_ledger | 1 | index1 | 1 | parent_id | A | 10129 | NULL | NULL | YES | BTREE | |
| general_ledger | 1 | index1 | 2 | location_id | A | 12661 | NULL | NULL | YES | BTREE | |
| general_ledger | 1 | index1 | 3 | gl | A | 25323 | NULL | NULL | YES | BTREE | |
| general_ledger | 1 | index1 | 4 | general_ledger_id | A | 25323 | NULL | NULL | YES | BTREE | |
| general_ledger | 1 | index2 | 1 | gl | A | 120 | NULL | NULL | YES | BTREE | |
| general_ledger | 1 | index2 | 2 | description | A | 7235 | 20 | NULL | YES | BTREE | |
+----------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.01 sec)


>
> > > 3) 'EXPLAIN <query> \G' for the
> three queries
>
> I meant: execute the command 'EXPLAIN SELECT ....'
> for each of the three queries and post the
> output.
> But please use \G instead of ; at then end of the
> command to keep the output readable.
>

WOW ... that rocks. Had no idea.

Query 1:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: i2
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 9352
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: gl2
type: ref
possible_keys: index1
key: index1
key_len: 5
ref: dts.i2.id
rows: 5
Extra: Using where
2 rows in set (0.01 sec)

Query 2:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: gl
type: ref
possible_keys: index1,index2
key: index2
key_len: 5
ref: const
rows: 1419
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: i
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dts.gl.parent_id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: lc
type: ref
possible_keys: index1
key: index1
key_len: 10
ref: dts.i.id,const
rows: 1
Extra: Using where; Distinct
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: l
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8844
Extra: Using where; Distinct
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: c
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dts.l.company_id
rows: 1
Extra: Distinct
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: co
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dts.l.country
rows: 1
Extra: Using index; Distinct
6 rows in set (0.00 sec)


Combined Query:

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: gl
type: ref
possible_keys: index1,index2
key: index2
key_len: 5
ref: const
rows: 1419
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: i
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dts.gl.parent_id
rows: 1
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: PRIMARY
table: lc
type: ref
possible_keys: index1
key: index1
key_len: 10
ref: dts.i.id,const
rows: 1
Extra: Using where
*************************** 4. row ***************************
id: 1
select_type: PRIMARY
table: l
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8844
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: PRIMARY
table: c
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dts.l.company_id
rows: 1
Extra: Distinct
*************************** 6. row ***************************
id: 1
select_type: PRIMARY
table: co
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dts.l.country
rows: 1
Extra: Using index; Distinct
*************************** 7. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: i2
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 9352
Extra: Using where
*************************** 8. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: gl2
type: ref
possible_keys: index1
key: index1
key_len: 5
ref: dts.i2.id
rows: 5
Extra: Using where
8 rows in set (6 min 23.28 sec)

Hope that helps ... looks Greek to me. :)