List:General Discussion« Previous MessageNext Message »
From:brian Date:July 3 2012 5:47pm
Subject:Re: alternative to slow query
View as plain text  
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.
Thread
alternative to slow querybrian3 Jul
  • Re: alternative to slow queryyoku ts3 Jul
    • Re: alternative to slow querybrian3 Jul
      • RE: alternative to slow queryBenjamin Stillman3 Jul
        • Re: alternative to slow querybrian3 Jul
          • RE: alternative to slow queryBenjamin Stillman3 Jul
            • Re: alternative to slow querybrian3 Jul
              • RE: alternative to slow queryRick James16 Jul
                • Re: alternative to slow querybrian17 Jul