MySQL select query returns 250K+ results, delete query only deletes one?

Question

I don't understand this. I've dropped the table and recreated it multiple times, and it's the same every time. When I run this query:

SELECT * FROM `squares` WHERE MBRContains(PolyFromText('Polygon((-34 166, -34 171, -41 171, -41 166, -34 166))'), `coordinate`);

I'm returned over 250,000 results, which is correct. When I try and alter the query to delete this based on my where clause, like so:

DELETE FROM `squares` WHERE MBRContains(PolyFromText('Polygon((-34 166, -34 171, -41 171, -41 166, -34 166))'), `coordinate`);

MySQL sees fit to only delete a single row at a time. I'm not going to click 'Go' 250K+ times. Why is this? Here's the table structure...

`squares` (
   `square_id` int(7) unsigned NOT NULL,
   `ref_coord_lat` double(8,6) NOT NULL,
   `ref_coord_long` double(9,6) NOT NULL,
   `coordinate` point NOT NULL,
   PRIMARY KEY (`square_id`),
   KEY `ref_coord_lat` (`ref_coord_lat`),
   KEY `ref_coord_long` (`ref_coord_long`),
   SPATIAL `coordinate` (`coordinate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

If it's any use, this is MySQL 5.5.27, running on phpMyAdmin 3.5.2.2. Any ideas?

@Mihai, is this what you mean? `Database client version: libmysql - mysqlnd 5.0.10 - 20111026` — marked-down, Aug 18 '13 at 21:38
...but the where conditions of the select and the delete you posted are different, so it is to be expected the number of results found would differ.... — Kzqai, Aug 18 '13 at 21:44
@Kzqai, Sorry about that. I was trying two different queries (both of which return in excess of 200K results). Both however only delete 1 row at a time. — marked-down, Aug 18 '13 at 21:46

score 0 · Answer 1 · answered Aug 18 '13 at 21:42

0

Try optimising your table squares.

EDIT: Or add a limit:

DELETE FROM `squares` WHERE MBRContains(PolyFromText('Polygon((-42 175, -42 179, -48 179,     -48 175, -42 175))'), `coordinate`) LIMIT 10000;

Play with the limit parameter if you can.

answered Aug 18 '13 at 21:42

Mihai

26,325
7
66
81

Optimized, added a limit. Still no difference. – marked-down Aug 18 '13 at 21:52

score 0 · Answer 2 · answered Aug 18 '13 at 22:23

That behavior strikes me as very odd, (if the client isn't slipping in a LIMIT 1 clause into the DELETE query.)

I can suggest a workaround. Use the SELECT query that returns the 250K+ rows, and use that as an inline view, and join back to the table to perform the delete.

DELETE t.*
  FROM `squares` t
  JOIN ( SELECT s.square_id
           FROM `squares` s 
          WHERE MBRContains(
                  PolyFromText(
                    'Polygon((-34 166, -34 171, -41 171, -41 166, -34 166))'
                  )
                  , s.`coordinate`
                )
       ) s
    ON s.square_id = t.square_id

(Test it as a SELECT first, replacing the DELETE keyword with SELECT.)

This doesn't answer the question of why this is happening (something peculiar with the behavior of the MBRContains or PolyFromText functions?) But it a possible workaround. The SELECT in the inline view should be materialized as a derived table (temporary MyISAM table), and then the delete should work against that. (Similar to creating a temporary table, inserting the results from a SELECT into it, and then referencing the table in another query.)

MySQL select query returns 250K+ results, delete query only deletes one?

2 Answers2