Table scan in join on primary keys??
I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table scan in join on primary keys??
If those were your real queries, I'd say a table scan is appropriate, as you are asking for every row (no WHERE condition, just a join). This would be a lot easier to answer if you'd actually show us the EXPLAIN output. Michael Nick Arnett wrote: I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table scan in join on primary keys??
If those were your real queries, I'd say a table scan is appropriate, as you are asking for every row (no WHERE condition, just a join). This would be a lot easier to answer if you'd actually show us the EXPLAIN output. Michael Nick Arnett wrote: I'm updating one table with data from another and finding that the server is doing a table scan on the second table, even though it's a simple join on the primary keys. This doesn't seem right. The query looks liket this: UPDATE a SET a.y TO b.a WHERE a.primary_key = b.primary_key and if I do the equivalent SELECT: EXPLAIN SELECT * FROM a, b WHERE a.primary_key = b.primary_key it shows that it's doing a table scan on table b. Is this normal? Is it because primary keys are unique? I had imagined that this would be a very fast operation, but it's not, it's surprisingly slow. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
