Query in loop becomes really slow

[OsakaWebbie]

I was having timeout troubles with a certain PHP batch script, and narrowed down the problem to one MySQL statement that is executed in a loop with different values for comparison (it's checking for similar records to data already acquired in another database). Here is one example of the query:

SELECT p.PersonID, p.FullName, p.HouseholdID, h.LabelName,
IF(LOWER(REPLACE(p.FullName,' ',''))!='第一宣教バプテスト教会'
  AND LOWER(REPLACE(REPLACE(p.Furigana,' ',''),',',''))!='daiichisenkyoubaptistchurch',1,0)
  AS hhonly
FROM kizuna_crash_test.person p
LEFT JOIN kizuna_crash_test.household h ON p.HouseholdID=h.HouseholdID
WHERE LOWER(REPLACE(p.FullName,' ',''))='第一宣教バプテスト教会'
  OR LOWER(REPLACE(REPLACE(p.Furigana,' ',''),',',''))='daiichisenkyoubaptistchurch'
  OR LOWER(REPLACE(REPLACE(h.LabelName,' ',''),',','')) LIKE '%第一宣教バプテスト教会御中%'
  OR (h.PostalCode='277-0014' AND h.Address='3-8-34')
  OR REPLACE(h.Phone,'-','')='0471649159'

(Except for the ID fields, the fields in the query are all VARCHAR of varying lengths, and there are about 10,000 records in each table. There are indexes, but due to the functions they are not being used.)

The execution times might start out less that a millisecond each, but at some point in the loop (sometimes the very first time, sometimes a few or a few dozen iterations later) they will suddenly get slow (typical times 200-300 milliseconds) and stay that way. I can only assume some sort of memory issue, but is there something bad I'm doing in the construction of the query to cause it to use massive memory? At the moment this is a one-off to process about 800 times, so I could break it up into smaller chunks to stay under the time limit, but I might use the same code in a more permanent and dynamic situation in the future, so I should figure out what is going on.

 

[DonQuichote]

I see that you want the data to be in some format, but allow other formats to be used and therefore use the functions. It would probably make a large difference if you could just apply the format one for every record of the table. If you do not want to loose the current entries, you could do that in extra fields, but you could just store, for example, telephone numbers as numbers only.

You could also create a temporary table, store all the comparison strings in them, and then build just one query based on a join with the temporary table. Queries in loops are not exactly clean programming practise.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)