Eahh, this query is a resource hog - I need a SQL guru!

I have this query and it's working ... But way too slooooow!
Anyone here who can speed this up for me or give a hint what to do?

If domains table has 1000 records it take about 20 sec to run,
but if it has 10000 records, it takes forever ... almost ...

Domains table will grow over time, to maybe say max 10000 records.
Links table will grow faster and soon hold several hundred thousand records.

I can live with an executing time of up to an hour when all tables populated, since this run as cron ...
But not forever ... I don't have time to wait ... I need speed

  $sql = "SELECT id, member_id, category_id, verified
  FROM domains
  WHERE

  (
  id NOT IN (SELECT in_domain_id  FROM links WHERE  in_domain_id='$domain_id')
  OR
  id NOT IN (SELECT out_domain_id FROM links WHERE out_domain_id='$domain_id')
  )

  AND
  link_in_counter < '".$max_links."'
  AND
  member_id <> '$member_id'
  AND
  random_in_counter > '0'
  AND
  category_id = '$category_id'
  AND
  verified = '2'
  ORDER BY id ASC";

Code (markup):

 

this might speed it up. instead of:

id NOT IN (SELECT in_domain_id FROM links WHERE in_domain_id='$domain_id')
OR
id NOT IN (SELECT out_domain_id FROM links WHERE out_domain_id='$domain_id')

try:

SELECT count(id) = 0 FROM links WHERE in_domain_id='$domain_id'
OR
SELECT count(id) = 0 FROM links WHERE out_domain_id='$domain_id'

At least I think you are just trying to find domains that don't have an in_domain_id or out_domain_id in the links table.

Let me know if that doesn't work and I'll give it another go.

 

Yep, your'e right ... I'm trying to find those domains with no id in links table.

I'll try your suggestion and let you know shortly how it works...

 

For what its worth this could be refined further into one query:

SELECT count(id) = 0 FROM links WHERE out_domain_id='$domain_id' or in_domain_id='$domain_id'

 

Okay, I tested now ..

At the start my db has:
500 dummy members
1000 dummy domains
0 Links

Now I run it once every 10 min as cron for testing:

------------------------
1st run:
Links created: 3033
Links Total: 3033
Run Time: 10 sec
---
2nd run:
Links created: 3058
Links Total: 6091
Run Time: 36 sec
---
3rd run:
Links created: 2975
Links Total: 9066
Run Time: 61 sec
------------------------

You see, problem still there, executing time incres way too fast.
Imagine how it will look after a few hundred runs and
200,000+ links in links table ...

Pleeeease give a second go if you can

 

I'll give it a try, it can only be better ... At least one query less to do ;-)

 

I'd like to give it a try, but it is tough without the database. What exactly are you trying to achieve? Are you adding dummy data to the database? A little background might help.

 

I tested

SELECT count(id) = 0 FROM links WHERE out_domain_id='$domain_id' or in_domain_id='$domain_id'

Code (markup):

now and this was worst!? Looks like first run will take more than 20 min ?!?
I tested it two times and rebooted both apache and MySQL between ...
Same result ...

 

I will pm u ...

 

I assume as you run it as cron you will put the output of this query into a new table ?

then you can speed it up by removing the 'order by id asc' in your query, and making id in the table the clustered pk


if you want more speed: experiment with this:
<> and not in is also not very optimal
so leave out the " AND member_id <> '$member_id'" and run a delete query on the output table
if this is faster or not depends on the data, but it's worth a try

also run a trace and see if there are table scans or it uses indexes, ..