Skip to main content
4 votes
Accepted

Is COMPACT a better format for fixed length rows than DYNAMIC?

For the table you show, I don't think COMPACT or DYNAMIC row format makes any significant difference. In modern MySQL, DYNAMIC row format is the default. It is pretty much the same as COMPACT, except ...
Bill Karwin's user avatar
  • 14.8k
3 votes

Is COMPACT a better format for fixed length rows than DYNAMIC?

FIXED vs DYNAMIC made a tiny difference in MyISAM. For InnoDB, there is essentially no such thing. COMPACT vs DYNAMIC says what happens when a "big" column exists in a row. CHAR(3) does ...
Rick James's user avatar
  • 78.1k
3 votes

Is COMPACT a better format for fixed length rows than DYNAMIC?

Another option would be to use multiple joins, to avoid grouping and counting and leveraging the fact that after the first few joins there are less and less rows satisfying the conditions: SELECT t1....
Andrea B.'s user avatar
  • 1,558
2 votes

Is COMPACT a better format for fixed length rows than DYNAMIC?

Count is notoriuos for being slow. But one of the performace problem can be addressed.by eliminating the IN clause, which would need to make a full table scan, ehivh xou would see if you make an ...
nbk's user avatar
  • 8,264
1 vote

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

Attempt #1: putting your condition in the WHERE clause works but it changes the query results as it will remove all rows with type!=5: SELECT t1.name, t1.type, t2.col1, t2.col2 FROM table1 t1 LEFT ...
bobflux's user avatar
  • 1,486
1 vote
Accepted

LEFT JOIN LATERAL with a function result, how to prevent unnecessary function calls?

SELECT t0.name, t0.type, t2.col1, t2.col2 FROM table1 t0 LEFT JOIN table1 t1 ON t0.id = t1.id AND t1.type = 5 LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.id IS NOT NULL; fiddle ...
Akina's user avatar
  • 19.9k
1 vote
Accepted

Many items in `IN` clause causing full table scan earlier than expected

The actual culprit When exploring the suggestion from @Akina, I noticed that there was a warning output from the SQL execution which I hadn't noticed before. This warning spells it all out: +---------+...
msbit's user avatar
  • 121
1 vote

Is COMPACT a better format for fixed length rows than DYNAMIC?

MySQL 5.7+ supports ngram indexing for full-text search. The chances are that this will be faster than your higher level implementation of the same feature. Set ngram_token_size=3 and you have trigram ...
Gordan Bobić's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible