0

I have a table with two columns: "users" has a full text index, "x" is a simple int column. The table contains just under 2 million entries. Using match...against to select rows containing a certain user returns quickly.

Searching by the value of x (which is not indexed) returns in ~3 seconds.

However, when I combine the two the query takes ~9 seconds! If anything, I'd expect the combined query to take far less time since the full text index cuts the possible rows by an order of magnitude. Even forgetting the full text index and using like "%___%" is faster!

What's going on here? How can I fix it?

The mySQL output is included below:

mysql> desc testing;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| users | varchar(120) | YES  | MUL | NULL    |       |
| x     | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select count(*) from testing;
+----------+
| count(*) |
+----------+
|  1924272 |
+----------+
1 row in set (3.56 sec)

mysql> select count(*) from testing where match(users) against("shy");
+----------+
| count(*) |
+----------+
|   149019 |
+----------+
1 row in set (0.42 sec)

mysql> select count(*) from testing where x>0;
+----------+
| count(*) |
+----------+
|  1924272 |
+----------+
1 row in set (3.62 sec)

mysql> select count(*) from testing where match(users) against("shy") and x>0;
+----------+
| count(*) |
+----------+
|   149019 |
+----------+
1 row in set (8.82 sec)

mysql> select count(*) from testing where users like "%shy%" and x>0;
+----------+
| count(*) |
+----------+
|   149019 |
+----------+
1 row in set (3.57 sec)
ReallyMadeMeThink
  • 1,061
  • 7
  • 11
  • 1
    What's your `innodb_buffer_pool_size`? Your index probably doesn't fit memory so you're I/O constrained by your disk. – N.B. Mar 20 '18 at 13:15
  • Can you add index to `x` column? – Grzegorz Mar 20 '18 at 13:26
  • @N.B. That did the trick! Upped the 'innodb_buffer_pool_size' to 1G and everything is under 1 second. Figured I was in the clear with that sort of stuff given that the 'match-against' only query returns without issue. Reply with an answer and I'll be happy to accept it. – ReallyMadeMeThink Mar 20 '18 at 13:30

2 Answers2

1

Always check the value of your innodb_buffer_pool_size and adjust it according to your system's capabilities and software requirements. This means don't give MySQL more RAM than you have :)

If the index doesn't fit the memory, MySQL will read it off the disk, constraining you to hard drive's speed. If you're on SSD, this can be ok-ish, but on mechanical drives it's slow as snail.

Indexes aren't as useful if they can't fit into RAM.

N.B.
  • 13,688
  • 3
  • 45
  • 55
0

Consider using a subquery e.g. 

select count(*) from (

    select * 

    from testing 

    where match(users) against("shy")

) shy_results 

where x>0;
syoels
  • 154
  • 1
  • 10