Speed up joins on thousands of rows

Question

I have two tables that look something along the lines of:

t1 (id INT PRIMARY KEY, data VARCHAR(10), fkey INT, INDEX i1(data))
t2 (id INT PRIMARY KEY, enabled TINYINT)

And a query to get data from t1. The query runs fine and takes a few ms to get data from it, although problems start appearing when I use joins. Here's an example of one of my queries:

SELECT `id` FROM `t1` WHERE `data`='something';

Which can return a few thousand rows, that might look something like:

1, something, 5
2, something, 5
3, something, 5
4, something, 6
5, something, 5

Out of these thousands of rows, there are probably only going to be one or two distinct values of fkey. Now, here's my query with the join:

SELECT `t1`.`id` FROM `t1` 
INNER JOIN `t2` 
   ON `t2`.`id`=`t1`.`fkey` 
   AND `t2`.`enabled`=1 
WHERE `t1`.`data`='something';

And here's the execution plan for it:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: index
possible_keys: id
          key: id
      key_len: 75
          ref: NULL
         rows: 378
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: i1
          key: i1
      key_len: 150
          ref: DB.t2.id,const
         rows: 13
     filtered: 100.00
        Extra: Using index

This query ends up taking roughly 60 seconds, compared to the few ms of the other query. I've also tried removing the enabled check from the query, but it has no impact on performance. Also, querying t2 directly by id only takes a few ms as well.

It seems to me like the database should only have a need to run the join on one or two rows and just cache the results for any rows with the same fkey, but that doesn't seem to be happening. Is there any way to achieve this behavior?

user_1367454 · Answer

you need to provide more information including query execution plan , but for starter, if you don't have an index on fkey you should add one and another one for data:

create index indx_name on t2(enabled)
create index indx_name2 on t1(id,fkey, data)

Joel_Coehoorn_3043 · Answer

Given the table definitions provided this already had the indexes I'd use.

You could argue for adding an index on t2 with both enabled and id, but since you're already looking up into t2 by unique indexed id I'm skeptical it'd help much.

Similarly, it's tempting to update INDEX i1(data) to INDEX i1(data, fkey) or INDEX i1(data, fkey, id), but for the latter option you end up using every field in the table and for the former you have to go back to the original table anyway.

Where this breaks down is if the table definitions for the question are incomplete relative to the real problem. If you have more fields in these tables the indexes above do start to look appealing to me, because they save potential disk activity relative to reference the full table.

user_14205776 · Answer

After a lot of searching, I've finally come across a good solution for this that significantly reduces the time it takes to run these queries.

This query:

SELECT `t1`.`id` FROM `t1` 
INNER JOIN `t2` 
   ON `t2`.`id`=`t1`.`fkey` 
   AND `t2`.`enabled`=1 
WHERE `t1`.`data`='something';

Can be rewritten as:

SELECT `t1`.`id` FROM `t1`
WHERE `t1`.`data`='something' 
AND `t1`.`fkey` IN (SELECT `id` FROM `t2` WHERE `t2`.`enabled`=1);

This will query and compare on all values from t2, which isn't ideal if it only needs one or two, but it's better than nothing.

If someone has a solution that does do the comparison on only a few and it isn't some MySQL hack then I'll mark it as accepted, but for now, this works pretty well.

EDIT: As @Joel Coehoorn and @eshirvana pointed out, indexing fkey in t1 is helpful, but only really has an affect on queries 8k+ rows.

Speed up joins on thousands of rows

3 Answers