Mysql – automate the “on” statement from MySQL queries

join;MySQLquery

Background

In the process of QA/QC, I am writing lots of queries with multiple joins. I am using MySQL.

I would like to know how (if) I can simplify join statements, e.g. by setting default fields to join on.

My tables are named with a plural form of the object that they contain:

 names
 types
 actions
 surnames
 names_surnames

where names_surnames is a many-many lookup table

the primary keys are always id and the foreign keys are always, e.g.

 names.type_id
 names.action_id
 names_surnames.name_id
 names_surnames.surname_id

My joins would be something like

select names.col1 
from names join types on names.type_id = type.id
           join actions on names.action_id = actions.id;

Question

Is there a way to get MySQL to always assume that tables will be joined with on thistables.thattable_id = thattable.id?

Best Answer

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;

Related Solutions

Mysql – way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date

When you say merge table, I assume you mean MERGE (Mrg_MyISAM) storage engine.

Without seeing any table definitions, I can easily say this...

The smartest way would be to have each MyISAM table defined with a very good primary key/unique key where the lead column would be the date. At the very least, create an index that uniquely identifies data with only one partition (one underlying MyISAM table).

Each MyISAM table under the Merge definition would have such an index.

Any other index where the date is not the first column would result in bunches of table scans (NOT PRETTY !!!)

I have two posts I made back in January 2012 on MERGE table usage:

Jan 04, 2012 : get column from too many tables in mysql
Jan 15, 2012 : Separate tables or partition one huge table?

While the Mrg_MyISAM storage engine is still around, most would favor creating a Partitioned Table instead. Notwithstanding, the same rule applies: create an index that uniquely identifies data with only one partition.

Mysql – Subqueries run very fast individually, but when joined are very slow

You don't need all the derived tables. You are joining the basic (product) too many times. You can write the query joining it only once.

Compound indices are a must for EAV designs. Try adding an index on (attribute_id, product_id, value) and then the query:

SELECT t0.id, 
       t1.`value` AS length, 
       t2.`value` AS height, 
       t3.`value` AS family
FROM
  products t0

INNER JOIN 
  product_eav_decimal t1
    ON  t1.product_id = t0.id  
    AND t1.attribute_id = 91
    AND t1.`value` BETWEEN 15 AND 35

LEFT JOIN
  product_eav_decimal t2
    ON  t2.product_id = t0.id  
    AND t2.attribute_id = 80  
-- 
-- 
--

LEFT JOIN                              -- LEFT or INNER join
  product_eav_decimal t6
    ON  t6.product_id = t0.id  
 -- AND t6.attribute_id = 

ORDER BY t0.id ASC ;