If there is an FAQ where this is addressed, please point me to it so
I can RTFM.
I need to find out if table joins lock the table or, alternatively,
how to find out what query is locking the table.
We're doing a miles-from-zipcode query and I'm looking to speed it up.
Since we only have a few of these queries and they are done
repeatedly, I thought it'd be good to pre-compute distances from the
specified zip. Something like this:
mysql> select * from zipDistances where origin=70118 limit 10;
+-------------+----------+--------+
| zip_distant | distance | origin |
+-------------+----------+--------+
| 70183 | 5 | 70118 |
| 70004 | 5 | 70118 |
| 70001 | 5 | 70118 |
| 70009 | 5 | 70118 |
| 70010 | 5 | 70118 |
| 70002 | 5 | 70118 |
| 70121 | 5 | 70118 |
| 70011 | 5 | 70118 |
| 70096 | 5 | 70118 |
| 70005 | 5 | 70118 |
+-------------+----------+--------+
We have a cron job that executes, takes an applicant and, for certain
clients, checks to see if the applicant is within a specified
distance.
Previously, we would pre-compute the list of zips within the
specified distance and create a query like this:
SELECT id FROM applicant WHERE zip in (70183,70004,70001,...)
All the possible zips would be listed out. I thought it would be
better to change it:
SELECT DISTINCT applicant.id FROM applicant,zipDistances
WHERE zip=zip_distant
AND origin = 70118
But, it looks like this join is locking the applicant table.
Does this sound plausible?
If it isn't plausible and I need to look elsewhere, how can I find
out what query is locking the table?
--
A choice between one man and a shovel, or a dozen men with teaspoons
is clear to me, and I'm sure it is clear to you also.
-- Zimran Ahmed <http://www.winterspeak.com/>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
