| 14:52.33 | *** join/#udbug fungus (n=olsonl@bromine.sosstaffing.com) |
| 15:12.24 | *** join/#udbug fungus (n=olsonl@bromine.sosstaffing.com) |
| 15:24.50 | *** join/#udbug brasto (n=brasto@166.70.44.49) |
| 15:24.50 | *** mode/#udbug [+o brasto] by ChanServ |
| 16:01.33 | *** join/#udbug SunSparc (n=sparky@66.7.114.125) |
| 16:10.42 | brasto | infobot, sic SunSparc |
| 16:10.49 | brasto | infobot, sick SunSparc |
| 16:10.49 | infobot | ACTION fetches Hagrid's Fluffy and sicks Fluffy on SunSparc. |
| 16:11.16 | brasto | practices pushing his 'k' key a few times |
| 16:13.49 | SunSparc | Nice Fluffy. Good boy. |
| 16:25.54 | eggyknap | generally spells that version of "sic[k]" without the k |
| 16:26.21 | eggyknap | http://en.wiktionary.org/wiki/sic |
| 16:28.44 | *** join/#udbug fungus (n=olsonl@bromine.sosstaffing.com) |
| 16:33.30 | brasto | ah, that's what I had thought originally, but when infobot responded to sick instead of sic, I thought I spelled it wrong |
| 16:33.36 | brasto | ~lart infobot |
| 16:33.36 | infobot | accelerates a free AOL cd to 50,000 rpm and lets brasto feel it |
| 16:33.42 | brasto | lol |
| 16:33.48 | brasto | ~dumb bot |
| 16:33.48 | infobot | stupid human |
| 17:09.32 | eggyknap | heh |
| 17:21.50 | brasto | Here's a query that finds the count of each domain for users in a table called 'logins': |
| 17:21.59 | brasto | SELECT |
| 17:21.59 | brasto | <PROTECTED> |
| 17:22.00 | brasto | <PROTECTED> |
| 17:22.00 | brasto | FROM logins |
| 17:22.00 | brasto | GROUP BY Domain |
| 17:22.00 | brasto | ORDER By Count DESC |
| 17:22.10 | brasto | anyone know a better way to do that? |
| 17:22.31 | brasto | I'm running this query on MySQL |
| 17:23.12 | brasto | the result was suprising to me. I thought hotmail would have the most, but Yahoo does. |
| 17:25.26 | eggyknap | Looks like the right way to do it. |
| 17:28.47 | brasto | I might try using some variables at the beginning of the statement to simplify the way the query looks, but it's not all that complicated anyway... |
| 17:29.32 | brasto | now i'm working on a way to make that query use a percent instead of a count |
| 17:29.48 | eggyknap | figures it would make it more complicated to add "simplifying" variables :) |
| 17:30.26 | eggyknap | You'll have to have a subquery to get the percent. One suggestion: make sure you don't end up with a correlated subquery. |
| 17:30.49 | eggyknap | And you could probably just use count(1) instead of count(substring(... |
| 17:36.57 | brasto | count(1)? |
| 17:38.52 | brasto | cool, that does work... |
| 17:39.11 | brasto | here's the percent subquery: |
| 17:39.13 | brasto | COUNT(SELECT id FROM logins WHERE cancel_date != '0000-00-00') |
| 17:39.20 | brasto | that's not working... |
| 17:39.30 | brasto | subquery for the denominator |
| 17:40.59 | brasto | any ideas eggyknap DexterTheDragon fungus SunSparc unum or macnewbold ? |
| 17:42.20 | fungus | Does the subquery work when run by itself? |
| 17:42.35 | fungus | Is cancel_date NULL? |
| 17:43.00 | brasto | yep, it works |
| 17:43.04 | brasto | by itself |
| 17:43.18 | brasto | Null isn't allowed |
| 17:43.22 | brasto | in that field |
| 17:45.00 | brasto | ah, i think the subquery's SELECT should be on the other side of COUNT |
| 17:45.56 | brasto | well... not exactly... that way, but I'll post it in a bit |
| 17:51.04 | brasto | turns out it works either way - the problem was with space |
| 17:51.20 | brasto | you can't have white space before or after the / when dividing |
| 17:52.09 | eggyknap | giggles at MySQL's crappy parser |
| 17:52.21 | eggyknap | brasto: what's the query now? |
| 17:53.29 | brasto | SELECT |
| 17:53.29 | brasto | <PROTECTED> |
| 17:53.29 | brasto | <PROTECTED> |
| 17:53.30 | brasto | FROM logins |
| 17:53.30 | brasto | WHERE cancel_date != '0000-00-00' |
| 17:53.30 | brasto | GROUP BY Domain |
| 17:53.32 | brasto | ORDER By Percent DESC |
| 17:53.58 | brasto | now i just need to round the percent off somewhere. |
| 17:58.29 | eggyknap | guesses that that version will be inordinately slow. |
| 17:59.55 | eggyknap | If mysql supports it, I'd suggest something more like select substring(...) Domain, count(1)/totalcount*100 Percent from logins, (select count(id) totalcount from logins where cancel_date != '0000-00-00') WHERE ... |
| 18:00.13 | eggyknap | The idea is that the total count is calculated only once, instead of once per row. |
| 18:00.36 | eggyknap | But perhaps MySQL's optimizer is smart enough to flatten that out, but it's really unlikely |
| 18:00.58 | brasto | I wondered if there was something like that, i'll look for it |
| 18:06.18 | brasto | eggyknap: COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. |
| 18:06.44 | brasto | that applies to MyISAM tables |
| 18:06.56 | brasto | but i have a where clause in that subquery |
| 18:07.54 | brasto | perhaps that's where a variable storing the resullts from one query will come in handy if there isn't an equivalent to totalcount(). |
| 18:10.05 | eggyknap | I expect a where clause will make that optimization moot. MyISAM tables are a good idea if you're not particularly fond of your data. :) |
| 18:10.28 | brasto | lol |
| 18:11.27 | SunSparc | Data is over-rated |
| 18:11.37 | eggyknap | Data *are* ! :) |
| 18:11.50 | SunSparc | I sit corrected |
| 18:40.05 | brasto | i tried this: |
| 18:40.06 | brasto | http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows |
| 18:40.13 | brasto | but the results were different... |
| 18:46.56 | brasto | ok, got it: |
| 18:47.01 | brasto | SET @totalrows = (SELECT COUNT(id) FROM logins WHERE cancel_date != '0000-00-00'); |
| 18:47.01 | brasto | SELECT |
| 18:47.02 | brasto | <PROTECTED> |
| 18:47.02 | brasto | <PROTECTED> |
| 18:47.02 | brasto | FROM logins |
| 18:47.02 | brasto | WHERE cancel_date != '0000-00-00' |
| 18:47.04 | brasto | GROUP BY Domain |
| 18:47.06 | brasto | ORDER By Percent DESC |