From: brian
Date: July 3 2012 5:47pm
Subject: Re: alternative to slow query
List-Archive: http://lists.mysql.com/mysql/227766
Message-Id: <4FF3301C.9090209@logi.ca>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 12-07-03 01:13 PM, Stillman, Benjamin wrote:
> I don't see an index for expression.id.
>

mysql db_lexi > show index from expression\G
*************************** 1. row ***************************
        Table: expression
   Non_unique: 0
     Key_name: PRIMARY
Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 96111
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:

... and 10 other keys, 2 of which are also being used in the WHERE part. 
I left them out for clarity because while they help to narrow things 
down a bit including them doesn't speed up the query all that much.



mysql db_lexi > SHOW TABLE STATUS WHERE NAME = 'expression'\G
*************************** 1. row ***************************
            Name: expression
          Engine: InnoDB
         Version: 10
      Row_format: Compact
            Rows: 100747
  Avg_row_length: 193
     Data_length: 19447808
Max_data_length: 0
    Index_length: 31621120
       Data_free: 48234496
  Auto_increment: 240840
     Create_time: 2012-06-27 14:18:57
     Update_time: NULL
      Check_time: NULL
       Collation: utf8_general_ci
        Checksum: NULL
  Create_options:
         Comment:
1 row in set (0.77 sec)

mysql db_lexi > SHOW TABLE STATUS WHERE NAME = 'expression_expression'\G
*************************** 1. row ***************************
            Name: expression_expression
          Engine: InnoDB
         Version: 10
      Row_format: Compact
            Rows: 106191
  Avg_row_length: 103
     Data_length: 11026432
Max_data_length: 0
    Index_length: 14204928
       Data_free: 48234496
  Auto_increment: 218884
     Create_time: 2012-06-27 14:19:31
     Update_time: NULL
      Check_time: NULL
       Collation: utf8_general_ci
        Checksum: NULL
  Create_options:
         Comment:
1 row in set (0.13 sec)

I realise that I should have posted all this in the original msg.