List:General Discussion« Previous MessageNext Message »
From:Martin Edelius Date:March 1 2000 1:49pm
Subject:Query optimization (what am I doing wrong?)
View as plain text  
Hi.

A couple of days ago I posted about a query that took forever to complete
and ate up all the HDD space on our server. I have since then read up on how
to optimize queries but I still can't get it to improve. I've read through
chapter 10 in the MySQL manual and I've read through the explanation of
EXPLAIN. I've read through some generic SQL books on the topic but they
provided very little info.

From what I understand I should make sure that the columns used in the query
are of equal size and that I use indexes on them. They are and I do.

I read that I should try and put the most restrictive critera so that the
optimizer reads it in first and because of this doesn't need to go through
the rest of the criterias but I can't figure out which way the MySQL
optimizer reads the query.

I read that I should try and avoid OR clauses but I really don't know how to
do that in this query.

I read that an index isn't used if it doesn't span all AND levels in the
WHERE clause. I'm having a hard time figuring out what this means in reality
and how I check if my indexes does this.

I've run isamchk -a on my tables to see if this would help, as it did in the
EXPLAIN examples, but this didn't matter at all either. I did a refresh
after the analyze.

I've included a textfile where I describe the tables, the indexes and the
result of an EXPLAIN of the query. I'll be getting me some more books to go
through but any and all ides on how to optimize this are more than
appreciated.

TIA.


Best regards,
Martin Edelius

Spirex Digital Design
--------------------------------
www: http://www.spirex.se
Mail: martin.edelius@stripped
Phone: +46-31-514651, 0708-113711
Fax: +46-31-514331
Aröds Industriväg 3c
S-422 43 Hisings Backa
--------------------------------
If I haven't got back to you or done what I'm supposed to, let me know again
as I have too much to do for my own good...

mysql> desc
> Common;
+---------------+------------------+------+-----+---------+----------------+
|
> Field         | Type             | Null | Key | Default | Extra         
> |
+---------------+------------------+------+-----+---------+----------------+
| UIV      
>     | varchar(30)      |      | PRI |         |                |
| Status        |
> varchar(30)      |      | MUL |         |                |
| Apparatagare  | varchar(30)  
>    |      |     |         |                |
| Modell        | varchar(30)      |      |
> MUL |         |                |
| Serienummer   | varchar(40)      |      | MUL |        
> |                |
| Felanmarkning | text             | YES  |     | NULL    |            
>    |
| Utfort_arbete | text             | YES  |     | NULL    |                |
|
> Emballage     | text             | YES  |     | NULL    |                |
| Tillbehor    
> | text             | YES  |     | NULL    |                |
| Ater_kund     | int(10)
> unsigned | YES  |     | NULL    |                |
| Debiteras     | text             |
> YES  |     | NULL    |                |
| Skador        | text             | YES  |     |
> NULL    |                |
| Servicenummer | int(10) unsigned |      | MUL | 0       |
> auto_increment
> |
+---------------+------------------+------+-----+---------+----------------+
13 rows in
> set (0.00 sec)

mysql> desc
> Inlamningsstalle;
+-------------------+------------------+------+-----+---------+----------------+
|
> Field             | Type             | Null | Key | Default | Extra         
> |
+-------------------+------------------+------+-----+---------+----------------+
|
> Inlamnad          | int(10) unsigned |      | MUL | 0       |                |
|
> Inlamningsstalle  | varchar(30)      |      | MUL |         |                |
|
> Kundfoljesedel    | varchar(30)      |      | MUL |         |                |
| Lopnummer
>         | int(10) unsigned |      | MUL | 0       | auto_increment |
| Betalare          |
> varchar(30)      |      | MUL |         |                |
| Belopp            | int(6)   
>        | YES  |     | NULL    |                |
| Paslag            | int(3)           |
> YES  |     | NULL    |                |
| Betalningssatt    | text             | YES  |   
>  | NULL    |                |
| Delbetalare       | varchar(30)      |      |     |       
>  |                |
| Delbelopp         | int(6)           | YES  |     | NULL    |       
>         |
| Delbetalningssatt | text             | YES  |     | NULL    |               
> |
| Inpris            | int(6)           | YES  |     | NULL    |                |
| Ater 
>             | date             | YES  |     | NULL    |                |
| Skickad        
>   | date             | YES  |     | NULL    |                |
| UIV               |
> varchar(30)      |      | PRI |         |               
> |
+-------------------+------------------+------+-----+---------+----------------+
15 rows
> in set (0.01 sec)

mysql> desc
> Insamlingsstalle;
+-------------------+------------------+------+-----+---------+----------------+
|
> Field             | Type             | Null | Key | Default | Extra         
> |
+-------------------+------------------+------+-----+---------+----------------+
|
> Inlamnad          | int(10) unsigned |      | MUL | 0       |                |
|
> Insamlingsstalle  | varchar(30)      |      | MUL |         |                |
| Lopnummer
>         | int(10) unsigned |      | MUL | 0       | auto_increment |
| Betalare          |
> varchar(30)      |      | MUL |         |                |
| Belopp            | int(6)   
>        | YES  |     | NULL    |                |
| Paslag            | int(3)           |
> YES  |     | NULL    |                |
| Betalningssatt    | text             | YES  |   
>  | NULL    |                |
| Delbetalare       | varchar(30)      |      |     |       
>  |                |
| Delbelopp         | int(6)           | YES  |     | NULL    |       
>         |
| Delbetalningssatt | text             | YES  |     | NULL    |               
> |
| Inpris            | int(6)           | YES  |     | NULL    |                |
| Ater 
>             | int(10) unsigned |      |     | 0       |                |
| Skickad        
>   | int(10) unsigned |      |     | 0       |                |
| UIV               |
> varchar(30)      |      | PRI |         |               
> |
+-------------------+------------------+------+-----+---------+----------------+
14 rows
> in set (0.00 sec)

mysql> desc
> Reparationsstalle;
+---------------------+----------------------+------+-----+---------+----------------+
|
> Field               | Type                 | Null | Key | Default | Extra         
> |
+---------------------+----------------------+------+-----+---------+----------------+
|
> Inlamnad            | int(10) unsigned     |      | MUL | 0       |                |
|
> Reparationsstalle   | varchar(30)          |      | MUL |         |                |
|
> Lopnummer           | int(10) unsigned     |      | MUL | 0       | auto_increment |
|
> Paborjad            | int(10) unsigned     |      |     | 0       |                |
|
> KF_lamnat           | int(10) unsigned     |      |     | 0       |                |
|
> KF_besvarat         | int(10) unsigned     |      |     | 0       |                |
|
> Betalare            | varchar(30)          |      | MUL |         |                |
|
> Belopp              | int(6)               | YES  |     | NULL    |                |
|
> Paslag              | int(3)               | YES  |     | NULL    |                |
|
> Betalningssatt      | text                 | YES  |     | NULL    |                |
|
> Delbetalare         | varchar(30)          |      |     |         |                |
|
> Delbelopp           | int(6)               | YES  |     | NULL    |                |
|
> Delbetalningssatt   | text                 | YES  |     | NULL    |                |
|
> Inpris              | int(6)               | YES  |     | NULL    |                |
|
> Skickad             | int(10) unsigned     |      |     | 0       |                |
|
> Ater                | int(10) unsigned     |      |     | 0       |                |
| UIV
>                 | varchar(30)          |      | PRI |         |                |
|
> Reparator           | varchar(30)          |      |     |         |                |
|
> Kostnad_moms        | smallint(5) unsigned |      |     | 0       |                |
|
> Kostnad_timtid      | smallint(5) unsigned |      |     | 0       |                |
|
> Kostnad_grundavgift | smallint(5) unsigned |      |     | 0       |                |
|
> Kostnad_admin       | smallint(5) unsigned |      |     | 0       |                |
|
> KF_forskott         | smallint(5) unsigned |      |     | 0       |               
> |
+---------------------+----------------------+------+-----+---------+----------------+
23
> rows in set (0.00 sec)

mysql> show index from
> Common;
+--------+------------+---------------+--------------+---------------+-----------+-------------+----------+
|
> Table  | Non_unique | Key_name      | Seq_in_index | Column_name   | Collation |
> Cardinality | Sub_part
> |
+--------+------------+---------------+--------------+---------------+-----------+-------------+----------+
|
> Common |          0 | PRIMARY       |            1 | UIV           | A         |         
> 34 |     NULL |
| Common |          1 | Status        |            1 | Status        | A  
>       |           3 |     NULL |
| Common |          1 | Serienummer   |            1 |
> Serienummer   | A         |          34 |     NULL |
| Common |          1 | Modell       
> |            1 | Modell        | A         |          11 |     NULL |
| Common |         
> 1 | Servicenummer |            1 | Servicenummer | A         |          34 |     NULL
> |
+--------+------------+---------------+--------------+---------------+-----------+-------------+----------+
5
> rows in set (0.00 sec)

mysql> show index from
> Inlamningsstalle;
+------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+
|
> Table            | Non_unique | Key_name         | Seq_in_index | Column_name      |
> Collation | Cardinality | Sub_part
> |
+------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+
|
> Inlamningsstalle |          1 | Inlamnad         |            1 | Inlamnad         | A    
>     |          34 |     NULL |
| Inlamningsstalle |          1 | Inlamningsstalle |       
>     1 | Inlamningsstalle | A         |           3 |     NULL |
| Inlamningsstalle |      
>    1 | Kundfoljesedel   |            1 | Kundfoljesedel   | A         |           3 |    
> NULL |
| Inlamningsstalle |          1 | Betalare         |            1 | Betalare       
>  | A         |          17 |     NULL |
| Inlamningsstalle |          1 | Lopnummer       
> |            1 | Lopnummer        | A         |          34 |     NULL |
|
> Inlamningsstalle |          0 | UIV              |            1 | UIV              | A    
>     |          34 |     NULL |
| Inlamningsstalle |          1 | Inlamnad_2       |       
>     1 | Inlamnad         | A         |          34 |     NULL
> |
+------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+
7
> rows in set (0.00 sec)

mysql> show index from
> Insamlingsstalle;
+------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+
|
> Table            | Non_unique | Key_name         | Seq_in_index | Column_name      |
> Collation | Cardinality | Sub_part
> |
+------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+
|
> Insamlingsstalle |          1 | Inlamnad         |            1 | Inlamnad         | A    
>     |           1 |     NULL |
| Insamlingsstalle |          1 | Insamlingsstalle |       
>     1 | Insamlingsstalle | A         |           2 |     NULL |
| Insamlingsstalle |      
>    1 | Betalare         |            1 | Betalare         | A         |           1 |    
> NULL |
| Insamlingsstalle |          1 | Lopnummer        |            1 | Lopnummer      
>  | A         |          34 |     NULL |
| Insamlingsstalle |          0 | PRIMARY         
> |            1 | UIV              | A         |          34 |     NULL
> |
+------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+
5
> rows in set (0.00 sec)

mysql> show index from
> Reparationsstalle;
+-------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+
|
> Table             | Non_unique | Key_name          | Seq_in_index | Column_name       |
> Collation | Cardinality | Sub_part
> |
+-------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+
|
> Reparationsstalle |          1 | Inlamnad          |            1 | Inlamnad          | A 
>        |           1 |     NULL |
| Reparationsstalle |          1 | Reparationsstalle |  
>          1 | Reparationsstalle | A         |           1 |     NULL |
| Reparationsstalle
> |          1 | Betalare          |            1 | Betalare          | A         |         
>  1 |     NULL |
| Reparationsstalle |          1 | Lopnummer         |            1 |
> Lopnummer         | A         |          29 |     NULL |
| Reparationsstalle |          0
> | PRIMARY           |            1 | UIV               | A         |          29 |    
> NULL
> |
+-------------------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+
5
> rows in set (0.00 sec)

mysql> explain select C.UIV 
    -> from Common as
> C,Inlamningsstalle as Inl,Insamlingsstalle as Ins,Reparationsstalle as R
    -> where
> (Inl.Inlamningsstalle='88161993900608864' and C.UIV=Inl.UIV)
    -> or
> (Ins.Insamlingsstalle='88161993900608864' and C.UIV=Ins.UIV)
    -> or
> (R.Reparationsstalle='88161993900608864' and C.UIV=R.UIV)
    -> or
> C.Servicenummer='23'
    -> order by C.Servicenummer
> asc;
+-------+------+---------------------------+------+---------+------+------+------------+
|
> table | type | possible_keys             | key  | key_len | ref  | rows | Extra     
> |
+-------+------+---------------------------+------+---------+------+------+------------+
|
> C     | ALL  | PRIMARY,Servicenummer     | NULL |    NULL | NULL |   34 |            |
|
> Inl   | ALL  | Inlamningsstalle,UIV      | NULL |    NULL | NULL |   34 |            |
| R
>     | ALL  | Reparationsstalle,PRIMARY | NULL |    NULL | NULL |   29 |            |
| Ins
>   | ALL  | Insamlingsstalle,PRIMARY  | NULL |    NULL | NULL |   34 | where used
> |
+-------+------+---------------------------+------+---------+------+------+------------+
Thread
Query optimization (what am I doing wrong?)Martin Edelius1 Mar
  • Re: Query optimization (what am I doing wrong?)Tonu Samuel1 Mar
  • Re: Query optimization (what am I doing wrong?)Tonu Samuel1 Mar
  • Re: Query optimization (what am I doing wrong?)Tonu Samuel1 Mar
  • Re: Query optimization (what am I doing wrong?)Martin Edelius1 Mar
  • Re: Query optimization (what am I doing wrong?)Martin Edelius3 Mar
  • Re: Query optimization (what am I doing wrong?)Jan Dvorak3 Mar
  • Re: Query optimization (what am I doing wrong?)Martin Edelius3 Mar