Have Geek Brain

Optimize this MySQL query?

The following request takes FOREVER to execute (30 + hrs on Macbook w / 4gig ram). I am looking for ways to make it more efficient. Any thoughts are appreciated!

CREATE TABLE fc AS 
SELECT  threadid,
    title,
    body,
    date,
    userlogin
FROM f 
WHERE pid 
    NOT IN (SELECT pid FROM ft) ORDER BY date;

(table "f" is ~ 1 gigabyte / 1,843,000 rows, table "ft" is 168 MB, 216,000 rows))

+2


a source to share


6 answers


Try outer join (I think MySQL supports them now), not in:



create table fc as 
select f.threadid
     , f.title
     , f.body
     , f.date
     , f.userlogin 
from f 
left outer join ft 
  on f.pid = ft.pid 
where ft.pid is null 
order by date
+5


a source


Start with EXPLAIN PLAN to see what the optimizer says. Then run it when you make changes to see if they help.



I bet the correct query will be launched in minutes.

+2


a source


Add a clustered pid index for the fc and ft tables.

+1


a source


Make sure you have a pid index of ft. It looks like you are getting the full cross product instead of joining by index.

0


a source


There may be hidden costs. How long does it take to run this:

SELECT  count(*)
FROM f 
WHERE pid 
    NOT IN (SELECT pid FROM ft);

If it doesn't take long, then your slowness in the command could be MySQL, duplicating all the data, as the statement is executed in case it doesn't work and it should discard it. (I've seen this using SQL Server.)

Also: isn't it if you take out the ORDER BY clause?

0


a source


How many lines in f

will not match a line in ft

? As a last resort, if pid

unique in f

, your target table fc

will contain> 1.6m rows. If most of the lines end in B fc

, you'd better do it in two steps:

CREATE TABLE fc AS 
SELECT  threadid,
    title,
    body,
    date,
    userlogin
FROM f
ORDER BY date;

DELETE FROM fc
WHERE pid 
     IN (SELECT pid FROM ft);

By the way, can you omit the ORDER BY clause? This sort can cost a lot of cycles, again depending on how many rows there are in the target table.

Another thing to consider is the EXISTS proposal ...

CREATE TABLE fc AS 
SELECT  threadid,
    title,
    body,
    date,
    userlogin
FROM f 
WHERE NOT EXISTS 
    (SELECT pid FROM ft 
     WHERE ft.pid = f.id)
ORDER BY date;

... or in my two-step version ...

DELETE FROM fc
WHERE EXISTS
     (SELECT pid FROM ft 
 WHERE ft.pid = f.id);

EXISTS can be much faster than IN when the subquery generates many rows. However, as is always the case with tuning, benchmarking is a key criterion.

0


a source






More articles:


All Articles