Recommend
1 
 Thumb up
 Hide
9 Posts

BoardGameGeek» Forums » BoardGameGeek Related » BGG General

Subject: SQL Query Strings appearing at top of page rss

Your Tags: Add tags
Popular Tags: [View All]
Russell Howell                (What's Right?) (What's Wrong?)        
United States
Canoga Park
California
flag msg tools
Brought to you by Zik Zak
badge
Brought to you by Zik Zak
Avatar
mbmbmbmbmb
I seem to be getting SQL Query strings output on the tops of pages related to game lists in my collection and the collections of other users. Should this be going out for all to see, or was a debug line inadvertantly missed?

Example output:

Quote:

snipped


Link to example: http://www.boardgamegeek.com/viewcollection.php3?username=Al...

Thanks

Rusty
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Richard Young
United States
Phoenix
Arizona
flag msg tools
badge
Avatar
mbmbmbmbmb
This has been happening for a couple of days
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Shawn
United States
Pelham
New Hampshire
flag msg tools
badge
Avatar
mbmbmbmbmb
I actually Geekmailed aldie about this a day or so ago.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Scott Alden
United States
Dallas
Texas
flag msg tools
admin
badge
Aldie's Full of Love!
Avatar
mbmbmbmbmb
generalpf wrote:
This explains a lot about why Aldie has had so many problems with scaling. Using a varchar for a foreign key? Bad, bad database design. Ouch.


I dunno - it has an index. which makes it a direct lookup, anyway - 7 years old that table is. Someday I'll fix it.
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Billy McBoatface
United States
Lexington
Massachusetts
flag msg tools
KGS is the #1 web site for playing go over the internet. Visit now!
badge
Yes, I really am that awesome.
Avatar
mbmbmbmbmb
generalpf wrote:
Sorry, I do this stuff for a living. Anyway, even if it's indexed, to compare a foreign key to a primary key, it has to do a string comparison for the length of the string, and that's O(n). If it's comparing integers, that's a 32-bit comparison right on the CPU and it's much faster. You'd just have to join in the users table to get the FK you're looking for. Index the crap out of that table with a clustered index on username and you're good to go.

Also, I'm assuming the username fields are varchars, not chars? Variable record lengths also slow MySQL down.

Cheers!
Actually, it's not the comparison time that matters, that's pretty insignificant unless your db is all cached. It's actually the size on disk. An integer with a range of +/-2,000,000,000 is 4 bytes. A varchar (with a size limit of <255 bytes) is the 1 byte per character, plus a "length" byte. This is usually longer than an int, often much longer, and longer keys mean more levels in your index tree (which means more disk seeks as you look for your match).

If your database is all cached, then the disk seeks don't matter, and then the comparison time does become significant.

I wouldn't use a string as a foreign key, but then again BGG seems to run all right, and that's what counts in the end...
 
 Thumb up
 tip
 Hide
  • [+] Dice rolls
Front Page | Welcome | Contact | Privacy Policy | Terms of Service | Advertise | Support BGG | Feeds RSS
Geekdo, BoardGameGeek, the Geekdo logo, and the BoardGameGeek logo are trademarks of BoardGameGeek, LLC.