next »

[nutballs]

So.... here is the deal.
Sometimes when you are doing SQL, you might need to hit yourself in the head a few times with a hammer, because the way you KNOW you should do it is wrong.

This is running on a table with about 3million rows.
now the original query has been tried 20 ways, order by, rand, min, etc.
the basic info is that "InterlinkUsed" is a counter to force lesser used entries to get used first.

the new query is a subselect... go figure.
Now of course in this specific context, which you don't know all the pieces, I can just grab a few hundred records and assume that is enough. I even tried it with 5000 records and it worked just as well.

so the results are....
original query: 15 seconds (i know i know)
new query: .0025 seconds

Thats a holy-shit type of difference, and frankly is what was probably completely screwing up my network prompting the XSLT/Serialize translation from another post of mine. which now turns out might have been pointless, though a good learning exercise none the less.

original query:

Code:

select InterlinkID,fk_DomainID,InterlinkDomain,InterlinkAsin,InterlinkTitle
from interlinks
where InterlinkUsed=(select MIN(InterlinkUsed) used from interlinks where not fk_DomainID in ($domainidlist))
and InterlinkAsin<>'$asin'
and not fk_DomainID in ($domainidlist)
limit $maxlinks

New Query:

Code:

SELECT InterlinkID, fk_DomainID, InterlinkDomain, InterlinkAsin, InterlinkTitle
FROM (
         SELECT InterlinkID, fk_DomainID, InterlinkDomain, InterlinkAsin, InterlinkTitle
         FROM interlinks 
         ORDER BY InterlinkUsed ASC 
         LIMIT 500 
         )temp
WHERE InterlinkAsin <>'$asin'
and not fk_DomainID in ($domainidlist)
order by rand()
limit $maxlinks

[vsloathe]

Not to be a smarty pants, but I'm pretty sure I would have started with the second query. That's just the way I think.

No, I can think of two examples of apps I've written that use a query very similar in structure to the second one.

[nutballs]

the problem is the project has been an evolution over time.
so the original query made more sense originally, and as new features to deal with scaling got added, the query still stayed in its same basic shape.

for the way I have things running now, it is the better way obviously, but before when I was using the second query which I kept trying initially, it was way slower. don't remember why though.

obviously it was stupid issue. but it shows that sometimes subselects into temp tables is often way faster, even though often it is not because of the overhead of table creation and filling.

[perkiset]

I wish I had a nickel for every time I refactored code and thought about how utterly fucking stupid I am. Really, a single nickel per.