The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 19, 2019, 06:05:17 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: hit yourself in the head with a hammer sometimes.  (Read 2767 times)
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« on: November 25, 2008, 04:33:56 PM »

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
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #1 on: November 25, 2008, 04:57:53 PM »

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.
Logged

hai
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #2 on: November 25, 2008, 05:25:04 PM »

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.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: November 25, 2008, 06:06:43 PM »

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.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!