Miscellaneous
Last updated
Was this helpful?
Last updated
Was this helpful?
Information schema
✅
Views
✅
Window functions
🟠
Common table expressions (CTEs)
✅
Stored procedures
🟠
Cursors
✅
Triggers
✅
Some MySQL features are client features, not server features. Dolt ships with a client (ie. ) and a server (). The Dolt client is not as sophisticated as the mysql
client. To access these features you can use the mysql
client that ships with MySQL.
SOURCE
❌
Works with Dolt via the mysql
client
LOAD DATA LOCAL INFILE
❌
LOAD DATA INFILE works with the Dolt client. The LOCAL option only works with Dolt via the mysql
client
Dolt supports the following join hints:
JOIN_ORDER(,...)
✅
Join tree in scope should use the following join execution order. Must include all table names.
LOOKUP_JOIN(,)
✅
Use LOOKUP strategy joining two tables.
MERGE_JOIN(,)
✅
Use MERGE strategy joining two tables.
HASH_JOIN(,)
✅
Use HASH strategy joining two tables.
INNER_JOIN(,)
✅
Use INNER strategy joining two tables.
SEMI_JOIN(,)
✅
Use SEMI strategy joining two tables (for EXISTS
or IN
queries).
ANTI_JOIN(,)
✅
Use ANTI strategy joining two tables (for NOT EXISTS
or NOT IN
queries).
JOIN_FIXED_ORDER
❌
Join tree uses in-place table order for execution.
NO_ICP
❌
Disable indexed range scans on index using filters.
Join hints are indicated immediately after a SELECT
token in a special
comment format /*+ */
. Multiple hints should be separated by spaces:
Join hints currently require a full set of valid hints for all to be applied. For example, if we have a three table join we can enforce JOIN_ORDER on its own, join strategies on their own, or both order and strategy:
Additional notes:
If one hint is invalid given the execution options, no hints are applied and the engine falls back to default costing.
Join operator hints are order-insensitive
Join operator hints apply as long as the indicated tables are subsets of the join left/right.
Dolt currently supports table statistics for index and join costing.
Statistics are auto-collected by default for servers, but cab be manually collected by running ANALYZE TABLE <table, ...>
.
Here is an example of how to initialize and observe statistics:
Some workloads, like batch imports, perform strictly better without the overhead of statistics collection. In these cases, we can explicitly stop or purge (stop + delete) statistics on a running server:
A stopped-stats server can be restarted, or have a single collection cycle performed by an operator:
An environment variable can disable statistics on server reboots:
A rebooted server with stats turned off has no reversal mechanism at the moment. All stats operations are no-ops if a server starts with the above variables set.
Statistics automatically update for servers by default. Stats are stored in a database in .dolt/stats
separate from user data. This folder can safely be deleted offline.
Stats throughput can be lowered by raising the the dolt_stats_job_interval
variable, which indicates the milliseconds of delay between processing steps. The higher the delay and more branches in a database, the longer it will take for statistic updates to materialize. High delays reduce the fraction of runtime resources diverted to managing background statistics.
Stats can be disabled with the dolt_stats_enabled=0
variable.
Stats persistence can be disabled with the dolt_stats_memory_only=1
variable.
The stats in-memory cache accumulates new histograms proportionally to the write rate and stats update rate. Periodically, an update cycle will swap the currently active histogram buckets to a new in-memory map and clear the old set.
Stats garbage collection can be disabled with the dolt_stats_gc_enabled=0
variable.
Garbage collection frequency can be tuned with the dolt_stats_gc_interval
variable (default 1 hour).
Dolt exposes a set of helper procedures for managing statistics collection and use:
dolt_stats_stop
: clear queue and disable thread
dolt_stats_restart
: clear queue, refresh queue, start thread
dolt_stats_purge
: clear queue, refresh queue, clear cache disable thread
dolt_stats_once
: collect statistics once, ex: in sql-shell
dolt_stats_wait
: block on a full queue cycle
dolt_stats_gc
: block waiting for a GC signal
dolt_stats_flush
: block waiting for a flush signal
dolt_stats_info
: print the current state of the stats provider (optional '-short'
flag)
Some functions not supported, see
Only a few statements are not yet supported, see