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-l...@logi.ca]
Sent: Tuesday, July 03, 2012 1:47 PM
To: mysql@lists.mysql.com
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
