Re: Benefits of indexing
Hi
It was really a general question. I am trying to speed up some slow queries. All the tables have the correct indexes on. However the tables are very large. Most have around 200,000 rows. One has over a million. The question is - would we get an improvement if the tables were significantly smaller?
That was the first and general question.
However. If it is ok there is also a more specific question. In one case I am seeing that an index is not being used. A full table scan 'ALL' is being done. Even though both columns have indexes on them.
SELECT * FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field1
DESC:
table1.field1 : int(11) unsigned | NO | PRI | NULL | auto_increment
table2.field1 : int(11) | YES | MUL | NULL
INDEXES:
table1.field1 has a PRIMARY key on it.
table1.field one also has another key on it. (I added that).
table2.field1 has one index on it
This is the EXPLAIN:
id: 1
select_type: SIMPLE
table: table1
type: ALL
possible_keys: OrderID
key: NULL
key_len: NULL
ref: NULL
rows: 161655
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: table2
type: eq_ref
possible_keys: PRIMARY,id
key: PRIMARY
key_len: 4
ref: table2.field1
rows: 1
Extra: Using where
Why is it not using an index for table1?
I've tried using an INNER JOIN.
Could it be because one of the columns is NULL and the other NOT NULL. And/or one is an unsigned int and one is a signed int?
Thanks
--Justin Wyllie
Subject
Written By
Posted
Re: Benefits of indexing
April 10, 2014 04:18AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.