MySQL Forums
Forum List  »  Newbie

Re: Benefits of indexing
Posted by: Justin Wyllie
Date: April 10, 2014 04:18AM

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

Options: ReplyQuote


Subject
Written By
Posted
April 09, 2014 09:26AM
April 09, 2014 12:38PM
April 09, 2014 02:21PM
Re: Benefits of indexing
April 10, 2014 04:18AM
April 10, 2014 05:36AM
April 10, 2014 03:35PM


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.