slow query, sql code attached

[DBNovice]

I have a database that keeps records on the issue and failure of an item.
Currently, the database is poorly desisned; therefore I'm performing queries
to break the data into normalized tables and attempting to perform a "left
join" query to build a cross-reference table.

The left join query is currently taking nearly 2 hours for MySQL to process,
using Navicat as a front-end. My system specs are 1.4Mhz Pentium Processor
with 1GB of RAM running on Windows XP. The first table "CDID" has the
following layout:
TblID INT 11 Primary
Holder ID INT 11 Index
CD Serial VARCHAR 26 Index
Fail Rsn VARCHAR 5
CD Type CHAR 3
Holder ID-CD Serial as Normal Index
This table contains 6,671,610 records.

The second table "TranTBL"
CDTblID INT 11 Primary
Holder ID INT 11 Index
CD Serial VARCHAR 26 Index
CDExpire Dt Date 0
Expire Rsn VARCHAR 4
Fail Dt Date 0
CardType CHAR 3
Issue Dt Date 0
Expire Dt Date 0
Race CHAR 2
Gender CHAR 2
Marital Status CHAR 2
Ethnicity VARCHAR 2
Height Smallint 6
Weight Smallint 6
DOB Date 0
Holder ID-CD Serial as Normal Index
This table contains 5,879,210 records.

My sql code for the query is as follows:

SELECT

`CDID`.`Holder ID`,

`CDID`.`CD Serial`,

`CDID`.`Fail Rsn`,

`TranTBL`.`Fail Dt`,

`TranTBL`.`Issue Dt`,

`TranTBL`.`Expire Dt`

FROM

`CDID`,

`TranTBL`

WHERE

`CDID`.`Holder ID`= `TranTBL`.`Holder ID` AND

`CDID`.`CD Serial`= `TranTBL`.`CD Serial`

Explain Provides the following:

Table Type Possible keys key
key length ref
rows extra

IDCD All Holder ID-CD Serial Null
8 Null
6,671,610 Null

TranTBL ref Holder ID-CD Serial Holder ID-CD Serial 8
IDCD.Holder ID, IDCD.CD Serial 1 Using where

Thanks in advance for any assistance or suggestions that you can offer.

Trent

[2metre]

DBNovice wrote:

I have a database that keeps records on the issue and failure of an item.
Currently, the database is poorly desisned; therefore I'm performing queries
to break the data into normalized tables and attempting to perform a "left
join" query to build a cross-reference table.

The left join query is currently taking nearly 2 hours for MySQL to process, [snip]
Thanks in advance for any assistance or suggestions that you can offer.

1/ I don't see any left join query in your post!

2/ The explain output doesn't seem to match the tables/query you have
described. Can you confirm it is the correct one.

3/ Assuming the explain output _is_ correct, can you confirm that you
haven't omitted anything like ORDER BY or GROUP By or any other WHERE
clauses in your query?

4/ The reason it takes so long is that mysql is finding a need to scan
all the records in the first table. We need to identify why.

[DBNovice]

DBNovice wrote:
I have a database that keeps records on the issue and failure of an item.
Currently, the database is poorly desisned; therefore I'm performing queries
to break the data into normalized tables and attempting to perform a "left
join" query to build a cross-reference table.

2metre replied:

1/ I don't see any left join query in your post!

2/ The explain output doesn't seem to match the tables/query you have
described. Can you confirm it is the correct one.

3/ Assuming the explain output _is_ correct, can you confirm that you
haven't omitted anything like ORDER BY or GROUP By or any other WHERE
clauses in your query?

4/ The reason it takes so long is that mysql is finding a need to scan all
the records in the first table. We need to identify why.

Thanks for the reply 2metre! Sorry, I had to edit the query for sensitive
info that's why EXPLAIN results were inconsistent with the original query.
I didn't use ORDER BY or GROUP BY in the query... should I use them? Here
it is:

The left join query is currently taking nearly 2 hours for MySQL to process,
using Navicat as a front-end. My system specs are 1.4Mhz Pentium Processor
with 1GB of RAM running on Windows XP. The first table "CDID" has the
following layout:
TblID INT 11 Primary
Holder ID INT 11 Index
CD Serial VARCHAR 26 Index
Fail Rsn VARCHAR 5
CD Type CHAR 3
Holder ID-CD Serial as Normal Index
This table contains 6,671,610 records.

The second table "TranTBL"
CDTblID INT 11 Primary
Holder ID INT 11 Index
CD Serial VARCHAR 26 Index
CDExpire Dt Date 0
Expire Rsn VARCHAR 4
Fail Dt Date 0
CardType CHAR 3
Issue Dt Date 0
Expire Dt Date 0
Race CHAR 2
Gender CHAR 2
Marital Status CHAR 2
Ethnicity VARCHAR 2
Height Smallint 6
Weight Smallint 6
DOB Date 0
Holder ID-CD Serial as Normal Index
This table contains 5,879,210 records.

My sql code for the query is as follows:

SELECT

`CDID`.`Holder ID`,

`CDID`.`CD Serial`,

`CDID`.`Fail Rsn`,

`TranTBL`.`Fail Dt`,

`TranTBL`.`Issue Dt`,

`TranTBL`.`Expire Dt`

FROM

`CDID`,

Left Join `TranTBL` ON `CDID`.`Holder ID`= `TranTBL`.`Holder ID`

WHERE
`CDID`.`CD Serial`= `TranTBL`.`CD Serial`

Explain Provides the following:

Table Type Possible keys key
key length ref
rows extra

IDCD All Holder ID-CD Serial Null
8 Null
6,671,610 Null

TranTBL ref Holder ID-CD Serial Holder ID-CD Serial 8
IDCD.Holder ID, IDCD.CD Serial 1 Using where

Thanks in advance for any assistance or suggestions that you can offer.

Trent
"2metre" <2m****@xxxhersham.net> wrote in message
news:cm**********@hercules.btinternet.com... DBNovice wrote:

I have a database that keeps records on the issue and failure of an item.
Currently, the database is poorly desisned; therefore I'm performing
queries to break the data into normalized tables and attempting to
perform a "left join" query to build a cross-reference table.

The left join query is currently taking nearly 2 hours for MySQL to
process,

[snip]

Thanks in advance for any assistance or suggestions that you can offer.

1/ I don't see any left join query in your post!

2/ The explain output doesn't seem to match the tables/query you have
described. Can you confirm it is the correct one.

3/ Assuming the explain output _is_ correct, can you confirm that you
haven't omitted anything like ORDER BY or GROUP By or any other WHERE
clauses in your query?

4/ The reason it takes so long is that mysql is finding a need to scan all
the records in the first table. We need to identify why.

[2metre]

DBNovice wrote:

SELECT

`CDID`.`Holder ID`,

`CDID`.`CD Serial`,

`CDID`.`Fail Rsn`,

`TranTBL`.`Fail Dt`,

`TranTBL`.`Issue Dt`,

`TranTBL`.`Expire Dt`

FROM

`CDID`,

Left Join `TranTBL` ON `CDID`.`Holder ID`= `TranTBL`.`Holder ID`

WHERE
`CDID`.`CD Serial`= `TranTBL`.`CD Serial`

You're double-joining the tables. MySQL doesn't appear to be able to
optimise that: either drop the left join or the where clause and include
the conditions in a single join.

[DBNovice]

Thanks, 2metre!

I removed the WHERE clause and placed both conditions in the ON statement of
the LEFT JOIN, and query zipped away. One strange thing is that when I made
the changes above and ran EXPLAIN SELECT and got the following results:

Table Type Possible keys key
key length ref
rows extra

IDCD All Holder ID-CD Serial Null
8 Null
6,671,610 Null

TranTBL ref Holder ID-CD Serial Holder ID-CD Serial
28
IDCD.Holder ID, IDCD.CD Serial 1

Which meant the entire referenced table would be read as before; however, I
ran the query without EXPLAIN in which the query processed quickly. The
EXPLAIN SELECT results at the conclusion of the query were:

Table Type Possible keys key
key length ref
rows extra

IDCD All Holder ID-CD Serial Null
8 Null
1410 Null

TranTBL ref Holder ID-CD Serial Holder ID-CD Serial 8
IDCD.Holder ID, IDCD.CD Serial 1

Which is more efficient, but why didn't the original EXPLAIN SELECT produce
these results? Am I using it incorrectly?

Lastly, I ran SHOW PROCESSLIST from the command line console while this
query was running. The response stated that this query was "Sending Data".
Does this mean that the query had completed and was in the process of
sending the results(6+ million rows) to my client which is on the same
machine? If so, are there any methods to speed this process? My client
uses Navicat as GUI-front-end. Could Navicat be the bottleneck in the
"Sending data" process.

Once again, thanks!

"2metre" <2m****@xxxhersham.net> wrote in message
news:cm**********@hercules.btinternet.com...

DBNovice wrote:

SELECT

`CDID`.`Holder ID`,

`CDID`.`CD Serial`,

`CDID`.`Fail Rsn`,

`TranTBL`.`Fail Dt`,

`TranTBL`.`Issue Dt`,

`TranTBL`.`Expire Dt`

FROM

`CDID`,

Left Join `TranTBL` ON `CDID`.`Holder ID`= `TranTBL`.`Holder ID`

WHERE
`CDID`.`CD Serial`= `TranTBL`.`CD Serial`

You're double-joining the tables. MySQL doesn't appear to be able to
optimise that: either drop the left join or the where clause and include
the conditions in a single join.