I am developing a database, therefore some tables will have MyISAM Storage Engine and other InnoDB.
My question is this: are there problems when performing INNER JOIN with tables that handle different Storage Engine?
I am developing a database, therefore some tables will have MyISAM Storage Engine and other InnoDB.
My question is this: are there problems when performing INNER JOIN with tables that handle different Storage Engine?
Yes, there are problems, and of various types and levels, according to the tables that participate and the types of queries.
Among the problems you may encounter:
INSERT , UPDATE or DELETE
SELECT or others that involve a large amount of data. It is not generally a good practice to mix, much less if it is something that in many systems is part of the same nucleus that gives cohesion to any system or application, as is the case of data.
I would ask myself if there is any reason to keep a dangerous mix of two different engines, when you can choose one or the other ... thus giving uniformity and consistency to all the gear of your application.
A question similar to yours was raised in the counterpart of this site in English. One of the answers given is compelling , well documented and widely accepted in the OS community. I will allow you to add it here translated, because it explains with more details possible situations that could occur when using interspersed tables using MyISAM e InnoDB :
Whenever there are unions involving MyISAM e InnoDB , the InnoDB tables will end up having a table level blocking behavior instead of a row level blocking due to the% share MyISAM the query and MVCC * can not be applied to MyISAM data. MVCC can not even be applied to InnoDB in some cases.
MyISAM
From another perspective, if any table MyISAM is updated through INSERT , UPDATE or DELETE , tables MyISAM involved in a query JOIN will be blocked from other connections to the Base of Data and the query JOIN will have to wait until the tables MyISAM can be read. Unfortunately, if there is a mix of InnoDB and MyISAM in query JOIN , tables InnoDB would have to experience intermittent blocking as occurs with tables MyISAM in query JOIN because they are restricted for writing .
MySQL depends on the cardinality of the index to determine an optimized plan of EXPLAIN . The cardinality of the index is stable in tables MyISAM until there are many INSERT , UPDATE and DELETE in the table, by means of which OPTIMIZE TABLE can be executed periodically in the MyISAM tables. The cardinality of the index InnoDB is NEVER STABLE! If you execute SHOW INDEXES FROM * innodbtable *; , you will see that the cardinality of the index changes every time you execute that command. That's because InnoDB will dives in the index to estimate the cardinality. Even if you execute OPTIMIZE TABLE for a table InnoDB , that will only defrag the table. OPTIMIZE TABLE will execute ANALYZE TABLE internally to generate index statistics in the table. That works for MyISAM . InnoDB ignores it.
My advice is to do everything possible and convert everything to InnoDB and optimize your settings accordingly.
Believe it or not, there is still an open bug report on the use of queries involving tables in InnoDB / MyISAM during a SELECT FOR UPDATE . If you read it, summarize the resolution as follows: DO NOT DO IT!
* MVCC, (from its initials in English: M ulti V ersion C oncurrency C ontrol), or multiversion concurrency control, is a concurrency control method commonly used by database management systems to provide simultaneous access to the database and programming languages to implement transactional memory.