MySQL Forums
Forum List  »  Performance

Explain plan question
Posted by: Jonathan Cochran
Date: March 17, 2005 11:10AM

**EDIT** You can skip down to the bottom of this if you want to see the remaining quesiton, I figured out my original question while typing this out...**/EDIT**

I have a basic query that I am running and it is taking forever... Here are the details:

Table1 (2096287 records)
+--------------+---------
| Field | Type
+-----------------+------
| ID | int(11)
| patient | int(11)
| ... | int(11)
| ... | int(11)
| ... | int(11)
| ... | char(2)
| ... | int(11)
| ... | char(2)
| claim_number | char(25)
| ... | date
| ... | char(12)
| ... | int(11)
| ... | int(11)
| ... | int(11)
| ... | char(1)
| ... | int(11)
| ... | int(11)
+--------------+---------

Table2 (2099997 records)
+---------------+-----
| Field | Type
+---------------+-----
| ID | int(11)
| FIRST_NAME | char(30)
| LAST_NAME | char(30)
| ... | char(9)
| ... | char(30)
| ... | char(45)
| ... | char(20)
| ... | char(2)
| ... | char(10)
| ... | char(12)
| ... | char(1)
| ... | date
| ... | int(11)
+---------------+---------

I know that some of these column types need to be cleaned up (varchar and not char or vise versa)...
Table2.ID relates to Table1.patient
Here is the query I am trying to run:
------------------------------------------------------------------------------------
SELECT Table1.claim_number AS id
,CONCAT(Table2.last_name, ', ', Table2.first_name) AS name
FROM Table1 JOIN Table2 ON (Table2.id = Table1.patient)
WHERE MATCH(Table2.last_name) AGAINST('COCHRAN')
GROUP BY Table1.claim_number
,CONCAT(Table2.last_name, ', ', Table2.first_name)
-------------------------------------------------------------------------------------

Here is the explain plan that I get:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Table1
type: index
possible_keys: NULL
key: IDX_3
key_len: 29
ref: NULL
rows: 2096287
Extra: Using index; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: Table2
type: fulltext
possible_keys: PRIMARY,IDX_1
key: IDX_1
key_len: 0
ref:
rows: 1
Extra: Using where


IDX_3
ALTER TABLE `Table1` ADD INDEX `IDX_3` USING BTREE(`claim_number`, `patient`);

IDX_1
ALTER TABLE `Table2` ADD FULLTEXT `IDX_1`(`LAST_NAME`);

I have also tried making IDX_1 a regular index like IDX_3 is with the LAST_NAME column.

So I have a couple questions.
1. Why does Row 1 of the explain plan show "possible_keys: NULL" but then uses "key: IDX_3"?
2. What can I do to make this not search the entire Table1. I think that it is so slow because it is copying that entire table to a temp table...


Ok, so I fixed the problem as I was typing this out... I actually thought this is how it originally was set up before I noticed the issue and started making changes, but it must not have been. I needed to remove claim_number from IDX_3. It makes sence because you should only need to index the columns that are in your WHERE (ON for joins) clause. None the less I messed it up at some point...
After the change the explain changed to this:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: patient
type: fulltext
possible_keys: PRIMARY,Index_3
key: Index_3
key_len: 0
ref:
rows: 1
Extra: Using where; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: claim
type: ref
possible_keys: IDX_CLAIM_3
key: IDX_CLAIM_3
key_len: 4
ref: ifx.patient.ID
rows: 1
Extra: Using index
2 rows in set (0.00 sec)

Much Better =)

So my only question that remains is question 1 from above.
1. Why did Row 1 of the explain plan show "possible_keys: NULL" but then uses "key: IDX_3"?

Sorry for the long post when the real propblem is allready solved, but that explain plan still bothers me =P

Thanks in advance for any help you can provide!!
-Jonathan

Options: ReplyQuote


Subject
Views
Written By
Posted
Explain plan question
2947
March 17, 2005 11:10AM
1774
March 22, 2005 12:25PM


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.