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 to delete specific range of data from system queue data table?

due to sudden increase of my database .mdf file we check there are 4 internal queue message table are increase unexpectedly. so we decided to delete some range of data from these table.

we try this code:

declare @c uniqueidentifier while(1=1) begin     select top 1 @c = conversation_handle from dbo.queuename     if (@@ROWCOUNT = 0)     break     end conversation @c with cleanup end   

but its try to delete all data and due to space limitation the query cannot execute. my internal message queue table :

enter image description here

enter image description here

Can Touch Range Spells/Effects Function Through Armor?

I am planning on playing a Grung PC for an upcoming homebrew campaign. The Poisonous Skin feature in Grung racial traits state:

Any creature that grapples you or otherwise comes into direct contact with your skin must succeed on a DC 12 Constitution saving throw or become poisoned for 1 minute. (One Grung Above p.4)

Now, this would be a problem if I wanted to support my party. (ie. Touch range spells) Would coming "into direct contact with your skin" mean skin on skin contact, or would the poison affect still work on skin and armor contact? Furthermore, is this the same deal with touch range spells? I guess I’m trying to find out: Can I, as a Grung, cast a touch range spell, like Cure Wounds, and bypass the poison effects by specifying that I touch the target’s armor? I’m not worried about enemies, because they are enemies, but more for my fellow party member’s sake.

SQL – Date Range Broken Down by Individual Month and Year while maintaining the ID


I need help breaking down a date range, from an existing table, into the individual month and year of the date range while maintaining the Identification Number assigned to that date range. Thanks!

CREATE TABLE #TEMP (BILL_YR INT, BEGIN_DT DATETIME, END_DT DATETIME )  INSERT INTO #TEMP (BILL_YR, BEGIN_DT, END_DT) VALUES ('1','2019-01-01','2019-12-31'), ('2','2020-01-01','2020-12-31') 

EXPECTED RESULT

BILL_YR MONTH    YEAR 1       1        2019 1       2        2019 1       3        2019 1       4        2019 1       5        2019 1       6        2019 1       7        2019 1       8        2019 1       9        2019 1       10       2019 1       11       2019 1       12       2019 2       1        2020 2       2        2020 2       3        2020 2       4        2020 2       5        2020 2       6        2020 2       7        2020 2       8        2020 2       9        2020 2       10       2020 2       11       2020 2       12       2020 

Plotting a well defined function displays nothing for two-thirds of the range required

The plot in question concerns the second derivative of an inverse Laplace transform (ILT) of a function with five parameters. Here is the ILT

ClearAll["Global`*"] prod = (s - cr1) (s - cr2) (s - cr3) (s - cr4); LW = (1 + s)^2/(si prod); Print["symbolic W'=", Wp = D[InverseLaplaceTransform[LW, s, x], x]] 

Four parameters are functions of the fifth parameter "si", defined as the roots of a fourth order equation

cr = {cr1, cr2, cr3, cr4} =     s /. Solve[si s^2 + 107 s/5 + 10 ((1 + s)^(-2) - 1) - 1/10 == 0,       s]; 

Plotting the first derivative of the ILT takes .64

lx = 13; Timing[  pd = Plot[Evaluate[Wp /. si -> 1], {x, 0, lx},     PlotRange -> {{0, lx}, {0.0225, .0275}}]] 

Plotting of the second derivative of the ILT takes 14.84 and displays nothing for two-thirds of the range lx

    Wd = D[Wp, x]; Timing[Plot[(Wd /. si -> 1), {x, 0, lx},   PlotRange -> {{0, lx}, {-0.002, .002}}]] 

Do crossbows and other noted exceptions miss past their long range underwater?

The rules is written as

A ranged weapon attack automatically misses a target beyond the weapon’s normal range. Even against a target within normal range, the attack roll has disadvantage unless the weapon is a crossbow, a net, or a weapon that is thrown like a javelin (including a spear, trident, or dart).

My question is if this part:

A ranged weapon attack automatically misses a target beyond the weapon’s normal range.

Is a complete clause on its own independent of the next sentences, or is a part of the later sentences. Do all ranged weapon attacks miss past their normal range, or only weapons that aren’t listed as exceptions?

Does the Witch Bolt spell end if you use a bonus action, reaction, or movement (remaining within the spell’s range)?

The obvious things are:

  • ending it voluntarily
  • losing concentration
  • breaking the tether with range or cover
  • using a normal action on your turn for something other than witch bolt

What isn’t specified is whether or not the spell ends when you use a bonus action, movement or reaction. I would assume that reactions would be fine because they are quick spells and not on your turn. Movement is generally not considered an action in 5e afaik, so it seems fine too. The description is less clear about bonus actions however, because they occur on your turn and witch bolt states

On a hit, the target takes 1d12 lightning damage, and on each of your turns for the duration, you can use your action to deal 1d12 lightning damage to the target automatically. The spell ends if you use your action to do anything else. The spell also ends if the target is ever outside the spell’s range or if it has total cover from you.

BUT Bonus actions are fast and often without a somatic/verbal component, and most cases in the PHB refer to the normal action as "your action". Also, the action required for witch bolt is NOT casting a spell and so the "bonus spell + cantrip" rule is not applicable here. Witch bolt is presumably taking a mental effort to maintain, such that I wouldn’t be able to cast another normal action spell without losing focus on the beam, but for something like misty step, with only a verbal component and taking minimal effort (especially if it’s a wizard spell-mastery spell), it seems to me like it would be possible.

Anyone know if there’s an official ruling for this, or is it entirely up to the DM? If I were a DM I’d rule that it’s allowed, since it requires giving up your standard action, and you’re still limited to the 30ft tether AND line of sight, AND concentration, AND spell slots if you’re not a lvl 18 wizard. Compared to a high level blastlock hitting for 4d10+20+knockback with eblast each turn, with a cantrip that would allow a bonus spell on your turn and a reaction spell on someone else’s turn, witch bolt with bonus action and reaction (now that I think about the math) still seems underpowered at high levels. (especially now that I realize that subsequent turns don’t add the extra damage from a higher lvl spell slot. A lot of text for something not terrible useful. Still curious though.

TL:DR Can I maintain witch bolt on a target and move normally and use my bonus action to teleport around with misty step (provided I maintain line of sight and tether range), while using my reactions on enemy creature’s turns for counterspell or shield?

Does Telekinetic Projectile allow choosing both projectile and target within range even if they are further apart than 30 feet?

Telekinetic Projectile allows you to "hurl [an] object that is within range […] at the target". The spell’s range is 30 feet.

My reading of this is that both the target and the object must be picked from within 30 feet from the caster. But nothing states that the path that the projectile flies (from object to target) must be less than 30 feet.

Are my assumptions correct or have I missed any relevant rules?

Also, if so, does the path between them have to be clear as to not provide Cover for the target?

Object <- 30ft -> caster <- 30ft -> target        <-         ~60ft          ->