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

keyboard – MacBook Pro 2011 has faulty power button – How do I enable any key or lid open boot settings?

I’m working on an Early 2011 MacBook Pro 13″ which suffered a little water damage.

After cleaning the system I can get it to turn on by disconnecting the battery, then reconnecting it and connecting the charger. It automatically turns on and everything operates fine.

If I do a shutdown though, I have no way of turning it on without repeating the battery/charge sequence (which resets the date and time). It doesn’t respond to the power button press, any keyboard key presses or lid open.

I was hoping there is a way to enable a “turn on when lid open and/or any key” type setting. Is this possible?

The system is running MacOS X 10.12.6.

How do I decrypt message with RSA algorithm with modulus n = 302446877 and encryption key e 677?

Message: 196512625 25825210 250970710 140420423 22130212 288711674 26343056

Plaintext is grouped in blocks of 4 letters.

What I have so far =(

Fold(#1 n + #2 &, 0, %);
IntegerDigits(%, 4);
FromCharacterCode(%)

qemu – Guest Boot Frozen At Press any key to boot from CD or DVD

I have been attempting to create a Windows 10 Virtual Machine using QEMU through virt-manager on Manjaro Linux. I have enabled the passthrough of my GPU and my mouse and keyboard. I have created one CDROM drive with the Windows 10 installation media, another CDROM drive with the VirtIO drivers, and then a qcow2-formatted boot device. I have enabled the boot menu of QEMU to boot first from the install media, then the boot drive, and then the VirtIO drivers for now, so no conflicts should occur. The problem that I am having is that the boot sequence gets frozen at Press any key to boot from CD or DVD after I press a key. I have tried 2 different installation media. The first time Windows said the files were corrupt, and the second is this situation (current install media). The XML for the Virtual Machine is as follows:

<domain type="kvm">
  <name>windows10pro</name>
  <uuid>649b7447-d392-4800-8b1f-d1efebf6a662</uuid>
  <metadata>
    <libosinfo:libosinfo xmlns:libosinfo="http://libosinfo.org/xmlns/libvirt/domain/1.0">
      <libosinfo:os id="http://microsoft.com/win/10"/>
    </libosinfo:libosinfo>
  </metadata>
  <memory unit="KiB">16777216</memory>
  <currentMemory unit="KiB">16777216</currentMemory>
  <vcpu placement="static" current="1">4</vcpu>
  <os>
    <type arch="x86_64" machine="pc-q35-5.1">hvm</type>
    <loader readonly="yes" type="pflash">/usr/share/edk2-ovmf/x64/OVMF_CODE.fd</loader>
    <nvram>/var/lib/libvirt/qemu/nvram/windows10pro_VARS.fd</nvram>
    <bootmenu enable="yes"/>
  </os>
  <features>
    <acpi/>
    <apic/>
    <hyperv>
      <relaxed state="on"/>
      <vapic state="on"/>
      <spinlocks state="on" retries="8191"/>
      <vendor_id state="on" value="none"/>
    </hyperv>
    <kvm>
      <hidden state="on"/>
    </kvm>
    <vmport state="off"/>
  </features>
  <cpu mode="host-model" check="partial">
    <topology sockets="1" dies="1" cores="4" threads="1"/>
  </cpu>
  <clock offset="localtime">
    <timer name="rtc" tickpolicy="catchup"/>
    <timer name="pit" tickpolicy="delay"/>
    <timer name="hpet" present="no"/>
    <timer name="hypervclock" present="yes"/>
  </clock>
  <on_poweroff>destroy</on_poweroff>
  <on_reboot>restart</on_reboot>
  <on_crash>destroy</on_crash>
  <pm>
    <suspend-to-mem enabled="no"/>
    <suspend-to-disk enabled="no"/>
  </pm>
  <devices>
    <emulator>/usr/bin/qemu-system-x86_64</emulator>
    <disk type="file" device="disk">
      <driver name="qemu" type="qcow2"/>
      <source file="/var/lib/libvirt/images/windows10pro.qcow2"/>
      <target dev="vda" bus="virtio"/>
      <boot order="2"/>
      <address type="pci" domain="0x0000" bus="0x03" slot="0x00" function="0x0"/>
    </disk>
    <disk type="file" device="cdrom">
      <driver name="qemu" type="raw"/>
      <source file="/home/antonios/Downloads/Win10_2004_English_x64.iso"/>
      <target dev="sdb" bus="sata"/>
      <readonly/>
      <boot order="1"/>
      <address type="drive" controller="0" bus="0" target="0" unit="1"/>
    </disk>
    <disk type="file" device="cdrom">
      <driver name="qemu" type="raw"/>
      <source file="/home/antonios/Downloads/virtio-win-0.1.185.iso"/>
      <target dev="sdc" bus="sata"/>
      <readonly/>
      <boot order="3"/>
      <address type="drive" controller="0" bus="0" target="0" unit="2"/>
    </disk>
    <controller type="usb" index="0" model="qemu-xhci" ports="15">
      <address type="pci" domain="0x0000" bus="0x02" slot="0x00" function="0x0"/>
    </controller>
    <controller type="sata" index="0">
      <address type="pci" domain="0x0000" bus="0x00" slot="0x1f" function="0x2"/>
    </controller>
    <controller type="pci" index="0" model="pcie-root"/>
    <controller type="pci" index="1" model="pcie-root-port">
      <model name="pcie-root-port"/>
      <target chassis="1" port="0x10"/>
      <address type="pci" domain="0x0000" bus="0x00" slot="0x02" function="0x0" multifunction="on"/>
    </controller>
    <controller type="pci" index="2" model="pcie-root-port">
      <model name="pcie-root-port"/>
      <target chassis="2" port="0x11"/>
      <address type="pci" domain="0x0000" bus="0x00" slot="0x02" function="0x1"/>
    </controller>
    <controller type="pci" index="3" model="pcie-root-port">
      <model name="pcie-root-port"/>
      <target chassis="3" port="0x12"/>
      <address type="pci" domain="0x0000" bus="0x00" slot="0x02" function="0x2"/>
    </controller>
    <controller type="pci" index="4" model="pcie-root-port">
      <model name="pcie-root-port"/>
      <target chassis="4" port="0x13"/>
      <address type="pci" domain="0x0000" bus="0x00" slot="0x02" function="0x3"/>
    </controller>
    <controller type="pci" index="5" model="pcie-root-port">
      <model name="pcie-root-port"/>
      <target chassis="5" port="0x14"/>
      <address type="pci" domain="0x0000" bus="0x00" slot="0x02" function="0x4"/>
    </controller>
    <controller type="pci" index="6" model="pcie-root-port">
      <model name="pcie-root-port"/>
      <target chassis="6" port="0x15"/>
      <address type="pci" domain="0x0000" bus="0x00" slot="0x02" function="0x5"/>
    </controller>
    <controller type="pci" index="7" model="pcie-root-port">
      <model name="pcie-root-port"/>
      <target chassis="7" port="0x16"/>
      <address type="pci" domain="0x0000" bus="0x00" slot="0x02" function="0x6"/>
    </controller>
    <interface type="network">
      <mac address="52:54:00:a2:0c:c4"/>
      <source network="default"/>
      <model type="e1000e"/>
      <address type="pci" domain="0x0000" bus="0x01" slot="0x00" function="0x0"/>
    </interface>
    <input type="tablet" bus="usb">
      <address type="usb" bus="0" port="3"/>
    </input>
    <input type="mouse" bus="ps2"/>
    <input type="keyboard" bus="ps2"/>
    <video>
      <model type="qxl" ram="65536" vram="65536" vgamem="16384" heads="1" primary="yes"/>
      <address type="pci" domain="0x0000" bus="0x00" slot="0x01" function="0x0"/>
    </video>
    <hostdev mode="subsystem" type="pci" managed="yes">
      <source>
        <address domain="0x0000" bus="0x01" slot="0x00" function="0x0"/>
      </source>
      <address type="pci" domain="0x0000" bus="0x04" slot="0x00" function="0x0"/>
    </hostdev>
    <hostdev mode="subsystem" type="pci" managed="yes">
      <source>
        <address domain="0x0000" bus="0x01" slot="0x00" function="0x1"/>
      </source>
      <address type="pci" domain="0x0000" bus="0x05" slot="0x00" function="0x0"/>
    </hostdev>
    <hostdev mode="subsystem" type="usb" managed="yes">
      <source>
        <vendor id="0x1b1c"/>
        <product id="0x1b49"/>
      </source>
      <address type="usb" bus="0" port="1"/>
    </hostdev>
    <hostdev mode="subsystem" type="usb" managed="yes">
      <source>
        <vendor id="0x264a"/>
        <product id="0x1011"/>
      </source>
      <address type="usb" bus="0" port="2"/>
    </hostdev>
    <redirdev bus="usb" type="spicevmc">
      <address type="usb" bus="0" port="4"/>
    </redirdev>
    <redirdev bus="usb" type="spicevmc">
      <address type="usb" bus="0" port="5"/>
    </redirdev>
    <memballoon model="virtio">
      <address type="pci" domain="0x0000" bus="0x06" slot="0x00" function="0x0"/>
    </memballoon>
  </devices>
</domain>

unity – How to move one Game unit with every key press?

This solution is for grid based games. The idea is to have “turns”. A turn will happen regardless of whatever or not there is input or not. At the end of the turn the avatar will be aligned to the grid, and a turn always takes the same time.

Pseudo-code:

var current_time = get_current_time();
var elapsed_time = current_time - start_time;
var got_input = get_input();
input = got_input ?? input;
if (elapsed_time > turn_duration)
{
    elapsed_time -= turn_duration;
    start_time += turn_duration;
    start_position = end_position;
    end_position = compute_next_position(start_position, input);
}

current_position = interpolate(start_position, end_position, elapsed_time);

The duration of the turn can be computed from the velocity and the size of the grid. We know that speed = distance / time, solve for time:

speed = distance / time

=>

time * speed = distance

=>

time = distance / speed

Thus:

turn_duration = grid_side_length / speed;

We are storing where it was at the start of the “turn”, and where it will be at the end of the turn. Those positions should be aligned to the grid. We use the elapsed time since the start of the turn to compute the current position via interpolation.

When, the turn ends, we use the input and the last position to compute the next position. That would be the position plus the size of the grid in the direction dictated by input, if any.

You can buffer input if you like. You can treat no input as going in the same direction if you like.


We can make the end_position nullable (declare it Vector2? instead of Vector2), and take that as signal that no movement is happening at the moment. We can also work with end_time instead of elapsed_time. Similarly make end_time nullable.

var current_time = get_current_time();
var got_input = get_input();
input = got_input ?? input;

if (input != null && end_position == null)
{
    start_time = current_time;
    end_time = start_time + turn_duration;
    start_position = current_position;
    end_position = compute_next_position(start_position, input);
}

if (end_time == null || end_position == null)
{
    return;
}

if (current_time > end_time.Value)
{
    current_position = end_position.Value;
    end_position = null;
    end_time = null;
}
else
{
    current_position = interpolate(start_position, end_position.Value, elapsed_time);
}

The above code differs in that it will move when it gets some input, and stays there until new input arrive. Note, however, that it will not interrupt the current motion. I do not know what you may want to happen when a key is pressed while the object is moving, it varies from game to game. However, the key to interrupt the motion is to drop the check for end_position == null, and be careful to set the new end_position still on the grid.


In my implementation for Unity and Godot, I made a Motion struct to encapsulate some of this. It holds start and end position, and also duration, and will do the interpolation given the elapsed time since the start of the motion. I also ended up accumulating delta time (Time.deltaTime) instead of using current time.

My code looks something like this (this is Unity code):

if (!_motion.HasValue)
{
    var nextMotion = NextMotion(transform.position, GetNewTarget(), speed);
    if (nextMotion != null)
    {
        _motion = nextMotion;
        _time = 0.0f;
    }
}

if (!_motion.HasValue)
{
    return;
}

_time += Time.deltaTime;
var motion = _motion.Value;
if (_time > motion.Duration)
{
    _motion = null;
    transform.position = motion.End;
}
else
{
    transform.position = motion.GetCurrent(_time);
}

GetNewTarget() computes a target position based on input. In your case, it should check the input, and if there is any, add to the current position the length of the side of the grid in the appropriate direction.

NextMotion creates a new motion, given the current position, the target position, and the speed.

As you can imagine GetCurrent is a linear interpolation. Its code is simply:

return Start + ((End - Start) * time / Duration);

Where Duration is pre-computed as:

var diff = end - start;
var length = diff.magnitude;
Duration = length / speed;

If you want to allow interrupting motion, the code should look something like this:

var target = GetNewTarget();

if (!_motion.HasValue || _motion.Value.End != target)
{
    var nextMotion = NextMotion(transform.position, target, speed);
// ...

And GetNewTarget would work from the start position of the current motion if any, if there is no current motion the use the current position.

bip 32 hd wallets – Import private key in HSM Luna SA

I need some clarification on how keys are imported into a HSM Luna SA. I should import BIP 32 private keys of multisign wallets. There are currently two keys: one for the user and one held by the company that would like to start using HSMs.

I start with a general question: is it better to keep all the
individual private keys in the hsm or a single master key that is used
to encrypt the private keys of the wallets?

How are BIP32 keys imported into an HSM? Is the serialized format
used?
Are there any other keys that are needed for the import (wrap for
example)?

I accept other advice on implementing the BIP32 with the Luna.

bios – Blinking cursor/underscore on older computer when booting off Windows Installation USB Key prepared with the Windows Media Creation Tool

In attempting to install Windows 10 on a newly-installed SSD in an HP Pavilion DV9000 laptop, I created a Windows Installation USB Key using Ventoy and the Media Creation Tool. While I’m able to boot into Ventoy, and select the Windows.iso, I’m then faced with a blank screen with a blinking cursor.

What I’ve tried, without success:

  1. Waiting 20 minutes to see if something changes
  2. Using a different, similarly-prepared USB Key, in a different USB port
  3. Using Balena Etcher to prepare the USB Key
  4. Using a Windows ISO with both 32 and 64 bit versions of the OS
  5. Using Rufus to prep the USB Key with Partition Scheme: GPT and Target system: UEFI
  6. Look for options in the BIOS related to UEFI (there were none)
  7. Look for a BIOS upgrade (none officially available from HP since the machine is over 10 years old)
  8. Check to see if Q4OS loads (it does)

What is the impact of an exposed secret key for a JWT token implementation?

Whoever possesses the private key can create valid tokens where your system simply can not distinguish between a legitimate token and a token created by the attacker.

I am guessing you are not just using the expiry field but also the subject field sub, which is in short terms the logged in user.

With the private key, I can create a token with any subject I want, thus sign in as any user of your system.

As you stated, I can also add any other claim and you system has no choice but trust it, as I was able to create a valid signature.

It can not be stressed enough, but JWT heavily relies on the private key to stay absolutely private.

evaluation – What are key differences between InputNotebook and EvaluationNotebook?

I am still new to Mathematica but advancing to a point of programming notebook and cell manipulations using native commands. Sometimes they call for specifying InputeNotebook[] while at other times they call for EvaluationNotebook[]. What are their key differences?

If I am only working with a single open notebook which type do I have?

If my current open notebook is notebook.nb and wish to load a function saved to function.nb which type are they?

If I am unsure of which type I have which is the safest to use?

ubuntu – SSH/SCP with public key AND password authentication SIMULTANEOUSLY

The server I’m attempting to SCP a file to requires BOTH a password AND a public/private key. I’m trying to send the file from Ubuntu but I can only get scp to use either key OR password, but not both. If I have the public key in place then scp never prompts for a password, authenticates successfully, then the server drops the connection because no password was provided. Likewise, if the key is not present I get prompted to enter a password, authentication is successful but the server drops the connection because there’s not a private key.

I can successfully login to the server using WinSCP by configuring a password AND key, I just can’t figure out how to do this from Ubuntu.

If it matters, the server in question is ws_ftp running on Windows Server 2008.