Unruly query

Databases
1

I have a bit of an unruly query which I was hoping someone might have some ideas on how to optimize…

I have a document management system, and I need to extract data across 3 tables:

document
edrms
doctype

the document table has ~4,000,000 document records. The document table has a one to many relationship with edrms (so edrms has more than records than document), and a one to one relationship with doctype. These are the table designs:

edrms

docno
assignmentid
event
isindexed

document

docno
typecode

doctype

typecode
edrmstypecode

Basically what I need to do is get edrms.docno, edrms.assignmentid, and doctype.edrmstypecode. There is no direct link between edrms table to doctype, and needs to use the document table as a linking table. So to achieve the desired result I’m doing this:

SELECT count(*)
FROM edrms
INNER JOIN document ON document.docno = edrms.docno
INNER JOIN doctype ON doctype.typecode = document.typecode

This takes a long time (over 30 seconds) and it’s not the volume of data it’s returning, because I’m just doing a count.

Also, document.docno, edrms.docno, doctype.typecode, and document.typecode are indexed columns.

Obviously I realise it’s a lot of data, but can anyone think of a way of making it faster?

Thanks in advance,

BBB

2 
SELECT COUNT(*)
  FROM edrms

:slight_smile:

3

lol, maybe I should have added that the count was just for timing purposes, the actual select is:

SELECT edrms.docno, edrms.assignmentid, doctype.edrmstypecode

4

could you please do a SHOW CREATE TABLE for each table, and then do an EXPLAIN on the query