# Optimizing table... Copy to tmp table slow down

**URL:** <https://forums.percona.com/t/optimizing-table-copy-to-tmp-table-slow-down/1322>\
**Category:** Other MySQL® Questions\
**Created:** [January 3, 2010, 8:49am UTC](https://forums.percona.com/t/optimizing-table-copy-to-tmp-table-slow-down/1322 "2010-01-03T08:49:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![lavium](https://avatars.discourse-cdn.com/v4/letter/l/db5fbb/32.png) [@lavium](https://forums.percona.com/u/lavium)\
**Post date:** [January 3, 2010, 8:49am UTC](https://forums.percona.com/t/optimizing-table-copy-to-tmp-table-slow-down/1322/1 "2010-01-03T08:49:45Z")

</div>

Hi,

I’m working on a project where I need to find out what number of items one user has in common with another user.

I’ve simplified my tables while I’m working on optimizing these queries. Here they are:

CREATE TABLE users ( id int(10) unsigned NOT NULL auto\_increment, PRIMARY KEY (id)) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1\_general\_ci;CREATE TABLE items ( id int(10) unsigned NOT NULL auto\_increment, PRIMARY KEY (id)) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1\_general\_ci;CREATE TABLE items\_to\_users( id int(10) unsigned NOT NULL auto\_increment, item\_id int(10) unsigned NOT NULL, user\_id int(10) unsigned NOT NULL, PRIMARY KEY (id), UNIQUE KEY item\_id\_2(item\_id,user\_id)) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1\_general\_ci;

There may be millions of users, millions of items, and, of course, millions of rows in items\_to\_users. For now, I generated a temporary dataset of 10000 users, 10000 items, and 2 million items\_to\_users.

Here are the queries I’m using to find users (the top 100) with similar items:

CREATE TEMPORARY TABLE temp\_items as SELECT items\_to\_users.id,item\_id,user\_id FROM items\_to\_users WHERE user\_id = SOME\_USER\_ID;SELECT m1.user\_id, m2.user\_id, count(\*) AS in\_common FROM temp\_items AS m1 INNER JOIN items\_to\_users AS m2 ON m1.item\_id = m2.item\_id AND m1.user\_id \< m2.user\_id GROUP BY m1.user\_id, m2.user\_id HAVING in\_common \> 0 ORDER BY in\_common DESC limit 100;

An explain of the second query shows that it makes a temporary table (I guess due to the group by coming from different tables?) and that step (Copy to tmp) takes a really long time compared to the rest of the query. Is there any way I can cut this time down? Or optimize it?

Thanks for your help! )  
Tom

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [January 3, 2010, 6:13pm UTC](https://forums.percona.com/t/optimizing-table-copy-to-tmp-table-slow-down/1322/2 "2010-01-03T18:13:29Z")

</div>

This is a difficult problem. It’s basically an N-dimensional proximity search. I can’t think of a really efficient way to do this in a database – any database – but you might try some kind of fuzzy matching to get you closer, such as creating a signature of a person’s preferences. For example, are the items categorizable? Maybe you can assign each user a string of bits that reflects whether they have an item in a given category, and then do an initial match on this string. Within the results, do an exact match (if needed) to get the final set of users. If I think of anything else I’ll post more.