EXPLAIN SELECT *
FROM
pergunta AS p
JOIN pergunta_texto AS ptx ON p.pergunta_id = ptx.pergunta_id
JOIN categoria AS c ON p.categoria_id = c.categoria_id
JOIN usuario AS u ON p.usuario_id = u.usuario_id
ORDER BY p.pergunta_id DESC
LIMIT 0, 10;
+----+-------------+-------+--------+---------------------------------+--------------+---------+--------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+--------------+---------+--------------------------+------+---------------------------------+
| 1 | SIMPLE | c | ALL | PRIMARY | NULL | NULL | NULL | 12 | Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | PRIMARY,usuario_id,categoria_id | categoria_id | 2 | respostas.c.categoria_id | 1922 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 3 | respostas.p.usuario_id | 1 | |
| 1 | SIMPLE | ptx | eq_ref | PRIMARY | PRIMARY | 3 | respostas.p.pergunta_id | 1 | |
+----+-------------+-------+--------+---------------------------------+--------------+---------+--------------------------+------+---------------------------------+
Extended (I put an * there just to be clear):
select *
from `respostas`.`pergunta` `p`
join `respostas`.`pergunta_texto` `ptx`
join `respostas`.`categoria` `c`
join `respostas`.`usuario` `u`
where
(
(`respostas`.`ptx`.`pergunta_id` = `p`.`pergunta_id`)
and (`p`.`categoria_id` = `respostas`.`c`.`categoria_id`)
and (`respostas`.`u`.`usuario_id` = `p`.`usuario_id`)
)
order by `p`.`pergunta_id` desc
limit 0,10
The above takes about 51 seconds, I suppose it's because it's using temporary and filesort due to that ORDER BY, which wouldn't be necessary in the right order.
Using a STRAIGHT_JOIN I get the following, and takes half a second to perform:
+----+-------------+-------+--------+---------------------------------+---------+---------+--------------------------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+---------+---------+--------------------------+--------+-------+
| 1 | SIMPLE | p | index | PRIMARY,usuario_id,categoria_id | PRIMARY | 3 | NULL | 114125 | |
| 1 | SIMPLE | ptx | eq_ref | PRIMARY | PRIMARY | 3 | respostas.p.pergunta_id | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 2 | respostas.p.categoria_id | 1 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 3 | respostas.p.usuario_id | 1 | |
+----+-------------+-------+--------+---------------------------------+---------+---------+--------------------------+--------+-------+
Extended (I put an * there just to be clear):
select straight_join *
from
`respostas`.`pergunta` `p`
join `respostas`.`pergunta_texto` `ptx`
join `respostas`.`categoria` `c`
join `respostas`.`usuario` `u`
where
(
(`respostas`.`ptx`.`pergunta_id` = `respostas`.`p`.`pergunta_id`)
and (`respostas`.`c`.`categoria_id` = `respostas`.`p`.`categoria_id`)
and (`respostas`.`u`.`usuario_id` = `respostas`.`p`.`usuario_id`)
)
order by `respostas`.`p`.`pergunta_id` desc
limit 0,10
I have already tried to ANALYZE all the tables, but still no success. The tables pergunta (p), categoria (c) and usuario (u) are InnoDB and pergunta_texto (ptx) is MyISAM (using Fulltext).
I'm ok with using STRAIGHT_JOIN, however when I use it in a VIEW, it seems to forget it:
CREATE ALGORITHM=MERGE VIEW vw_pergunta_ultimas AS
SELECT STRAIGHT_JOIN
p.pergunta_id, p.notificar, ptx.pergunta, c.categoria_id, c.caminho, u.usuario_id, u.nick
FROM
pergunta AS p
JOIN pergunta_texto AS ptx ON p.pergunta_id = ptx.pergunta_id
JOIN categoria AS c ON p.categoria_id = c.categoria_id
JOIN usuario AS u ON p.usuario_id = u.usuario_id
ORDER BY p.pergunta_id DESC;
mysql> EXPLAIN EXTENDED SELECT * FROM vw_pergunta_ultimas LIMIT 0,10;
+----+-------------+-------+--------+---------------------------------+--------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------------------+--------------+---------+--------------------------+------+----------------------------------------------+
| 1 | SIMPLE | c | index | PRIMARY | caminho | 257 | NULL | 12 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | PRIMARY,usuario_id,categoria_id | categoria_id | 2 | respostas.c.categoria_id | 1922 | |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 3 | respostas.p.usuario_id | 1 | |
| 1 | SIMPLE | ptx | eq_ref | PRIMARY | PRIMARY | 3 | respostas.p.pergunta_id | 1 | |
+----+-------------+-------+--------+---------------------------------+--------------+---------+--------------------------+------+----------------------------------------------+
Extended:
select
`p`.`pergunta_id` AS `pergunta_id`,
`p`.`notificar` AS `notificar`,
`ptx`.`pergunta` AS `pergunta`,
`c`.`categoria_id` AS `categoria_id`,
`c`.`caminho` AS `caminho`,
`u`.`usuario_id` AS `usuario_id`,
`u`.`nick` AS `nick`
from
`respostas`.`pergunta` `p`
join `respostas`.`pergunta_texto` `ptx`
join `respostas`.`categoria` `c`
join `respostas`.`usuario` `u`
where
(
(`ptx`.`pergunta_id` = `p`.`pergunta_id`)
and (`p`.`categoria_id` = `c`.`categoria_id`)
and (`u`.`usuario_id` = `p`.`usuario_id`)
)
order by `p`.`pergunta_id` desc
limit 0,10
As you can see, it didn't use the STRAIGHT_JOIN.
Any tips?
Version 5.0.38-Debian_1-log