MariaDB (MySQL) slow query when primary key range combined with fulltext index

I’ve a table described below, with two columns – integer primary key and title text – currently holding circa 3 million records. As seen in the metadata below, there’s a BTREE index on integer primary key column, and FULLTEXT index on title column.

MariaDB [ttsdata]> describe records; +------------------+---------------------+------+-----+---------------------+-------------------------------+ | Field            | Type                | Null | Key | Default             | Extra                         | +------------------+---------------------+------+-----+---------------------+-------------------------------+ | id               | int(15) unsigned    | NO   | PRI | NULL                | auto_increment                | | title            | varchar(2000)       | YES  | MUL |                     |                               | +------------------+---------------------+------+-----+---------------------+-------------------------------+  MariaDB [ttsada]> show index from records; +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table   | Non_unique | Key_name                | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | records |          0 | PRIMARY                 |            1 | id               | A         |     2798873 |     NULL | NULL   |      | BTREE      |         |               | | records |          1 | title                   |            1 | title            | NULL      |           1 |     NULL | NULL   | YES  | FULLTEXT   |         |               | +---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 

I’d like to run the following query:

SELECT SQL_NO_CACHE * FROM records WHERE   id > 1928177 AND   MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 

This query takes more 5 seconds to execute. When I remove the the range part or the fulltext part, in both cases the query executes in circa 100 ms. Below is analysis of individual queries, the last one being the one I want to use.

I’m new to MySQL and DBA in general. I’ve posted EXPLAIN statements but I have no idea how to draw any conclusions from them. I assume that the query is slow because the range filtering happens on data set obtained from full text query.

So my question is: How can I make the query fast?

The 1928177 magic number is something that just happens to be needed.

Query 1

SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200; +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ | id   | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra                 | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ |    1 | SIMPLE      | records | range | PRIMARY       | PRIMARY | 4       | NULL | 227183 | Using index condition | +------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+ 1 row in set (0.005 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200; ... 200 rows in set (0.108 sec) 

Time: 0.108 sec

Query 2

SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id   | select_type | table   | type     | possible_keys | key   | key_len | ref  | rows | Extra       | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ |    1 | SIMPLE      | records | fulltext | title         | title | 0       |      | 1    | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.007 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (0.138 sec) 

Time: 0.138 sec

Query 3

SELECT SQL_NO_CACHE * FROM records WHERE   id > 1928177 AND   MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200 
MariaDB [ttsdata]> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ | id   | select_type | table   | type     | possible_keys | key   | key_len | ref  | rows | Extra       | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ |    1 | SIMPLE      | records | fulltext | PRIMARY,title | title | 0       |      | 1    | Using where | +------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.005 sec)  MariaDB [ttsdata]> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200; ... 200 rows in set (5.627 sec) 

Time: 5.627 sec

How are numerical bonuses combined?

Are there explicit rules for combining numerical bonuses in 5th edition, or is it just left implicitly to "common sense"? There are special cases (e.g. damage resistance and vulnerability, choice of base AC formulae, identical spell/item effects) which are well-defined, but I can’t find a general rule.

Additive and multiplicative bonuses

Example: Longstrider and Haste for a creature with 30′ base walking speed.

Longstrider: You touch a creature. The target’s speed increases by 10 feet until the spell ends.

Haste: Choose a willing creature that you can see within range. Until the spell ends, the target’s speed is doubled […]

Possible interpretations:

  • Always multiply first: (30′ × 2) + 10′ = 70′
  • Always add first: (30′ + 10′) × 2 = 80′
  • Do it in the order the spells were cast
  • Player chooses
  • GM chooses

Multiplicative bonuses with one another

Example: Haste and Boots of Speed for a creature with 30′ base walking speed.

Boots of Speed: While you wear these boots, you can use a bonus action and click the boots’ heels together. If you do, the boots double your walking speed […]

Possible interpretations:

  • Doubling the second time just idempotently repeats the fact that it’s been doubled: max(30′ × 2, 30′ × 2) = 60′
  • Doubling the second time means your speed is quadrupled: 30′ × 2 × 2 = 120′
  • Doubling the second time adds another 100% of your base movement, resulting in an overall tripling: 30′ × (100% + 100% + 100%) = 90′. This was the rule in 3rd edition.

Can Rainbow Servant be combined with Versatile Spellcaster and Spontaneous Divination to cast all Cleric spells spontaneously?

A ridiculous idea has occurred to me and I’m looking to verify if really works. Consider the following build:

  • Wizard 5/Rainbow Servant 10 (a class from Complete Divine)
  • At Wizard 5, take Spontaneous Divination from Complete Champion so that you qualify for Versatile Spellcaster from Races of the Dragon.
  • Take Versatile Spellcaster at some point.
  • At Rainbow Servant 10, you gain access to the entire Cleric spell list.
  • Put together, Versatile Spellcaster and Rainbow Servant 10 should let a Wizard cast spontaneously from the entire Cleric spell list.

This gives me two questions, the first as a focus and the second as a supplemental:

  1. Does this actually work? My first objection is that I’m not quite sure if the Wizard automatically knows all of the Cleric spells at Rainbow Servant 10. If you have to record them in your spellbook, you’ll soon run out of gold.
  2. If this does work, can the "powerful non-Diviner spellcaster class that can also cast spontaneously from the entire Cleric spell list" idea be done better? The term "Rainbow Warake" seems to be in my memory.

Can Living Gloves combined with Remove Curse be used to gain access to multiple artisan tool skills?

Living Gloves (p278 ERftLW) Says:

While attuned to these gloves, you gain one of the following proficiencies (your choice when you attune to the gloves):

  • Sleight of Hand
  • Thieves’ tools
  • One kind of artisan’s tools of your choice
  • One kind of musical instrument of your choice

Symbiotic Nature. The gloves can’t be removed from you while you’re attuned to them, and you can’t voluntarily end your attunement to them. If you’re targeted by a spell that ends a curse, your attunement to the gloves ends, and they can be removed.

Can remove curse (p271 PHB) be used to end the curse, then you can put the gloves back on to attune once again and choose a different proficiency?

Calculate combined standard deviation

If I have a data that I fit with NonlinearModelfit that fits a data based on two fitting parameters, c1 and c2.

When I used nlm["ParameterTable"] // Quiet I get the following table:

Image

If I have an equation such as:

eq = (2.303*((70 + 273.15)^2)*(c1/c2))/1000

Is there any code (as opposed to doing it manually) I can use to calculate the value of eq with the combined standard deviation based on the standard deviations of c1 and c2 from the table?

Thank you!

What DnD 5e build (with minimum number of combined levels over all classes) is needed to summon all summonable monsters?

What build would make if you just want to be able summon all monsters (of the summonable monsters) without a general purpose spell like wish or special equipment – how many levels of a multiclass do you need at minimum? Specify background. I believe a Wizard (Summoning school) can nearly summon all monster types with CR limits but for instance has to choose between demons and animals at level 4.

Unity: How to prevent animation from twitching when combined with Navmesh Agent

I am trying to make a third person shooter in Unity and trying to implement a zombie that follows the player and when close to player, attack animation is played. I am using the NavMesh Agent for that purpose. But I am facing the problem that once the zombie comes close and if the player moves back or side ways, the zombie twitches and the transition between the walking and attacking animation appears broken. Also, it slides on the floor around the player. Here is the video of it: Video Here is my code to control the zombie movement:

using System.Collections; using System.Collections.Generic; using UnityEngine; using UnityEngine.AI;  public class ZombieController : MonoBehaviour {     public Animator animator;     public NavMeshAgent agent;     public GameObject player;      public float turnSmoothing = 15f;               public float speedDampTime = 0.1f;     private const float stopDistanceProportion = 0.1f;      private int health = 100;      private PlayerContorller pc;      // Start is called before the first frame update     void Start()     {         agent.updateRotation = false;         agent.SetDestination(player.transform.position);         agent.isStopped = true;         pc = player.GetComponent<PlayerContorller>();     }       // Update is called once per frame     void Update()     {         agent.SetDestination(player.transform.position);          if (agent.pathPending)             return;         float speed = 0.0f;         if (!pc.Dead)         {             if (agent.remainingDistance <= agent.stoppingDistance * stopDistanceProportion)             {                 agent.isStopped = true;                 animator.SetBool("PlayerClose", true);                 Quaternion targetRotation = Quaternion.LookRotation(player.transform.position - transform.position);                 transform.rotation = Quaternion.Lerp(transform.rotation, targetRotation, turnSmoothing * Time.deltaTime);             }             else if (agent.remainingDistance < 70.0f)             {                 Quaternion targetRotation = Quaternion.LookRotation(player.transform.position - transform.position);                 transform.rotation = Quaternion.Lerp(transform.rotation, targetRotation, turnSmoothing * Time.deltaTime);                 float proportionalDistance = 1f - agent.remainingDistance / agent.stoppingDistance;                  speed = Mathf.Lerp(0.5f, 1f, proportionalDistance);                 agent.isStopped = false;                 animator.SetBool("PlayerClose", false);             }             else if (agent.remainingDistance < 100.0f)             {                 Quaternion targetRotation = Quaternion.LookRotation(player.transform.position - transform.position);                 float proportionalDistance = 1f - agent.remainingDistance / agent.stoppingDistance;                 transform.rotation = Quaternion.Lerp(transform.rotation, targetRotation, turnSmoothing * Time.deltaTime);                 speed = Mathf.Lerp(0f, 0.5f, proportionalDistance);                 agent.isStopped = false;                 animator.SetBool("PlayerClose", false);             }         }         else         {             agent.isStopped = true;             animator.SetBool("PlayerClose", false);             speed = 0f;         }         animator.SetFloat("Speed", speed);     }      public void Hit()     {         health -= 10;         if(health == 0)         {             animator.Play("Die");             StartCoroutine(Die());          }     }      IEnumerator Die()     {         yield return new WaitForSeconds(10);         Destroy(gameObject);     } } 

The Speed parameter controls Idle (when 0), walking (0-0.5), running(0.5-1) of the zombie, all three are part of a blend tree. And when the PlayerClose parameter is set to true, the animation transitions to the attack animation. How do I fix this? what changes to make in order to fix that?

What is the AC of a character with 16 dex combined with the spell Armor?

The Spell Armor (Unearthed Arcana p.51-52) states:

The caster creates ,a magical field of force which serves as if it were leather armor (AC 8). If the spell is cast upon a person already armored, it has no effect. However, if it is cast upon a creature with an armor class normally better than 9 (due to its size, speed, skin, etc.) it will benefit the normal armor class by one step, i.e. AC 8 becomes 7, AC 7 becomes 6, and so on. The magic armor spell does not slow or hinder movement, adds no weight or encumbrance, nor does it prevent spell casting(…)

Normally,a character with 16 dex + a leather armor would have AC 6, what bugs me is the ”etc.” in the examples, I believe it is fair to say that my magic-user would have AC 6 since a comparison can be made with the real armor that it imitates, and ”etc.” might be other examples that I’m not aware of, but I don’t think the AC from dexterity is included in those examples.

In short:

Is my AC 6 or 7?

Can green flame blade, slashing flourish, and sweeping maneuver be combined for a massive sweeping attack?

A character with the classes: Rogue(Arcane Trickster) lvl 3, Bard (College of Swords) lvl 4, Fighter (Battle Master) lvl 3, has:

  1. Green Flame blade.
  2. The Bardic College of Sword’s slashing flourish.
  3. Battle Master’s sweeping maneuver.

Can these be used in the same turn against the same 2 foes?

Character is fighting 2 opponents, Casts Green Flame blade and hits target and therefore the “jumping flame” portion of the spell hits the 2nd target as well, and since the attack hit the player decides to use both the flourish and sweep to deal even more damage to the second target.

Do both damage sources stack?

Can both be used at the same time?

Both the slashing flourish and the sweeping maneuver do not require an action nor bonus action to activate. For the attack, would both the flourish and maneuver apply should the player choose to use both resources?

Is it just down to a DM call?