From: Benjamin Stillman
Date: July 3 2012 6:18pm
Subject: RE: alternative to slow query
List-Archive: http://lists.mysql.com/mysql/227767
Message-Id: <A1CBEF39AA63D340951BAE325A3BA5A7081E2D@COLMAILBOXP01.Limited.brands.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Not sure why it  wouldn't show primary as a possible key then...

From your first email:

*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: e
          type: ALL
possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 95127
         Extra:


I'd be curious to see the explain from this:

select id, lang, term from expression where id =3D (insert a random, valid =
id value here);

Does it use a key then? Or at least show primary as a possible key?




-----Original Message-----
From: brian [mailto:mysql-list@stripped]
Sent: Tuesday, July 03, 2012 1:47 PM
To: mysql@stripped
Subject: Re: alternative to slow query

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 =3D '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 =3D '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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql


________________________________

Notice: This communication may contain privileged and/or confidential infor=
mation. If you are not the intended recipient, please notify the sender by =
email, and immediately delete the message and any attachments without copyi=
ng or disclosing them. LBI may, for any reason, intercept, access, use, and=
 disclose any information that is communicated by or through, or which is s=
tored on, its networks, applications, services, and devices.