sql - MySQL query, how to optimize it better -

sql - MySQL query, how to optimize it better -

i have next queries 1 1 executing

delete secure_dl_ipmap timestamp < (now() - interval {$days} day)

select null secure_dl_ddown d d.id = id_file

delete secure_dl_ddown id not in (select i.id_file secure_dl_ipmap i)

secure_dl_ddown table

id:int(10) actuallink:text pretendname:varchar(100) whoreferred:text size:int(11)

no indexes. id, actuallink, whoreferred primary

about 100k rows;

secure_dl_ipmap table

id:int(10) id_file:int(10) ipaddress:varchar(15) dccode:varchar(30) timestamp:timestamp refer:text

no indexes. id & refer set primary

about 100k rows;

do have ideas how alter queries , if needed index tables?

add btree index on secure_dl_ipmap.timestamp , hash index on secure_dl_ddown.id:

alter table secure_dl_ipmap add together index ididx using btree (timestamp); alter table secure_dl_ddown add together index ididx using hash (id);

background btree indices best used range-queries (like "lower than"). hash indices fast on "equal" selectors.

edit

to speed delete operations may utilize delete quick ... stated here:

if going delete many rows table, might faster utilize delete quick followed optimize table. rebuilds index rather performing many index block merge operations.

edit2

following hint found on same page seek this:

delete secure_dl_ddown secure_dl_ddown left bring together secure_dl_ipmap on secure_dl_ddown.id=secure_dl_ipmap.id_file secure_dl_ipmap.id_file null

(to delete rows exist in t1 have no match in t2, utilize left join)

mysql sql query-optimization