List:General Discussion« Previous MessageNext Message »
From:Benjamin Stillman Date:July 3 2012 6:18pm
Subject:RE: alternative to slow query
View as plain text  
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 = (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 = '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.

--
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 information. If you
are not the intended recipient, please notify the sender by email, and immediately delete
the message and any attachments without copying or disclosing them. LBI may, for any
reason, intercept, access, use, and disclose any information that is communicated by or
through, or which is stored on, its networks, applications, services, and devices.
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