How about creating views for joins that are used repeatedly ?
For example:
create view my_view as
select names.id as name_id, names.type_id, names.action_id, ...
from names join types on names.type_id = type.id
join actions on names.action_id = actions.id;
In your particular case, I would use LEFT JOIN
instead. Why?
In a LEFT JOIN
query, you can dictate what keys you want and the order you want to fetch and join, while INNER JOIN
lets the MySQL Query Optimizer decide which is best. In some cases, the EXPLAIN plan may look horrible but still give good performance.
Instead of
SELECT *
FROM table
INNER JOIN (
SELECT id
FROM table
LIMIT 10 OFFSET 100000)
AS results USING(id);
flip the query to have the keys first
SELECT B.*
FROM (SELECT id FROM table LIMIT 10 OFFSET 100000) A
LEFT JOIN table B USING (id);
From there, you can control the order by inside in subquery A
SELECT B.*
FROM (SELECT id FROM table LIMIT 10 OFFSET 100000 ORDER BY ...) A
LEFT JOIN table B USING (id);
or after USING (id)
SELECT B.*
FROM (SELECT id FROM table LIMIT 10 OFFSET 100000) A
LEFT JOIN table B USING (id) ORDER BY ...;
This way, you should be able to navigate (or paginate) through the keys before locating actual data. To show this using empirical evidence, see my StackOverflow post (Fetching a Single Row from Join Table) on using LEFT JOIN
with primary keys.
Give it a Try !!!
Best Answer
As far as I know, a
JOIN [INNER JOIN]
, from the theoretical point of view, is a projection of aCROSS JOIN
(every combination of two tables). If you can get a cross join using a subquery and apply any function on it, then you have a perfect substitute. I think you can always transform:into:
A similar conclusion can be reached with
LEFT [OUTER] JOINS
andRIGHT [OUTER] JOINS
(the other types supported on MySQL), by including the NULL value: