I am running the following query:
SELECT p.val1, p.val2, p.val3, p.val4, p.val5, p.val6, p.val7, p.val8
FROM db1.tbl1 AS p
INNER JOIN db2.tbl2 vp ON p.pid = vp.pid
INNER JOIN db2.tbl1 AS vs ON vp.vid = vs.vid
INNER JOIN db3.tbl1 AS sa ON vs.sid = sa.sid
LEFT JOIN db4.tbl1 AS fs ON p.aid = fs.aid
WHERE sa.id = '11594'
AND fs.aid IS NULL
ORDER BY IF( (
ISNULL( egl )
OR egl = '' ) , 1, 0
), egl DESC
LIMIT 15
OFFSET 0
Unfortunately, it just hangs when run.
Running an EXPLAIN nets me this info:
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|*---*|*-----------*|*-----*|*-----*|*---------------------------------*|*----------*|*-------*|*----------*|*------*|*--------------
| 1 | SIMPLE | sa | const | PRIMARY,s_key,p_key,n_key,ignored | PRIMARY | 4 | const | 1 | Using filesort
| 1 | SIMPLE | p | ALL | PRIMARY, pid | NULL | NULL | NULL | 744704 |
| 1 | SIMPLE | vp | ref | PRIMARY,pid | pid | 130 | db1.p.pid | 1 | Using index
| 1 | SIMPLE | vs | ref | vid | vid | 130 | db2.vp.vid | 1 | Using where
| 1 | SIMPLE | fs | ref | a_key | a_key | 97 | func | 1 | Using where; Using index
If I and USE INDEX or FORCE INDEX after the FROM db1.tbl1 AS p, it does not change a thing.
My assumption is the problem is that table p isn't using any of the indexes. Is this assumption correct?
What are some reasons this query wouldn't use one of the possible keys?
The problem was with the ORDER BY clause. The dbms was attempting to apply it to db1.tbl1 before the joins (apparently). Wrapping the query in a select and putting the ORDER BY outside made the dbms work as expected.
SELECT * FROM
(SELECT p.val1, p.val2, p.val3, p.val4, p.val5, p.val6, p.val7, p.val8
FROM db1.tbl1 AS p
INNER JOIN db2.tbl2 vp ON p.pid = vp.pid
INNER JOIN db2.tbl1 AS vs ON vp.vid = vs.vid
INNER JOIN db3.tbl1 AS sa ON vs.sid = sa.sid
LEFT JOIN db4.tbl1 AS fs ON p.aid = fs.aid
WHERE sa.id = '11594'
AND fs.aid IS NULL) AS tmp
ORDER BY IF( (
ISNULL( egl )
OR egl = '' ) , 1, 0
), egl DESC
LIMIT 15
OFFSET 0