0

I have an insert-only table in MySQL named word. Once the number of rows exceeds 1000000, I would like to delete the first 100000 rows of the table.

I am using mysqldb in python, so I have a global variable:

wordcount = cursor.execute("select * from word")

will return the number of rows in the table in the python environment. I then increment the wordcount by 1 everytime I insert a new row. Then I check if the number of rows are greater than 1000000, if it is, I want to delete the first 100000 rows:

if wordcount > 1000000:
    cursor.execute("delete from word limit 100000")

I got this idea from this thread: Delete first X lines of a database

However, this SQL ends of deleting my ENTIRE table, what am I missing here?

Thank you.

Community
  • 1
  • 1
user1452494
  • 1,145
  • 5
  • 18
  • 40
  • 1
    You are shure that `wordcount = cursor.execute("select * from word")` returns the number of records ? – PeterMmm Jul 08 '13 at 14:09
  • In the python environment yes, in MySQL no. For example wordcount = cursor.execute("select count(*) from word") would return 1 in python, because it is 1 line. Whereas my SQL would return in the number of lines (in the python environment) which is the count – user1452494 Jul 08 '13 at 14:13
  • Did you read the `cursor` [doc](http://www.python.org/dev/peps/pep-0249/#cursor-objects) ? – PeterMmm Jul 08 '13 at 16:18
  • yes. I am mostly wonder if my why my sql is deleting the whole table instead of just the first 100000 rows – user1452494 Jul 08 '13 at 16:21
  • Find here example for fetching the row count: http://stackoverflow.com/questions/2511679/python-number-of-rows-affected-by-cursor-executeselect – PeterMmm Jul 08 '13 at 16:45

2 Answers2

0

I don't think that's the right way of getting the number of rows. You need to change your statement to have a count(*) and then use MySQLs cursor.fetchone() to get a tuple of the results, where the first position (kinda like wordcount = cursor.fetchone()[0]) will have the correct row count.

Your delete statement looks right, maybe you have explicit transactions? in which case you'd have to call commit() on your db object after the delete.

Ricardo Villamil
  • 5,031
  • 2
  • 30
  • 26
0

If your table "word" have ID field (key auto_increment field) you may write some stored procedure of deleting first 100000-rows. The key part of stored procedure is:

drop temporary table if exists tt_ids;
create temporary table tt_ids (id int not null);

insert into tt_ids -- taking first 100000 rows
select id from word
order by ID
limit 100000;

delete w
from word w
join tt_ids ids on w.ID = ids.ID;

drop temporary table if exists tt_ids;

Also you may build some indexes on tt_ids on ID-field for a speed-UP your query.

Alex
  • 28
  • 4