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)
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.