SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›6 »

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

My company's been running MySQL 4 and PHP 4, mainly because, well, it worked. Now we're testing a new server, and think it's time to upgrade both to version 5. However, when I migrated the 4 databases to the 5 test server, while some straight lookups work fine, some queries are killing the server. A search that took 1.2 seconds to run on the old server, I have to kill after it's run for 600 seconds on the new one. I'm guessing it has to do with it having LIKEs or JOINs or some crap that 5 doesn't like, or at least in an order that 5 doesn't like, but this brings up my question:

Is there a list of changes from MySQL 4 to MySQL 5 that a programmer should know about? Most I've seen is about the features, but what about coding quality? Obviously not all of the commands are handled identically to how they were before. I've googled all I can but I have yet to find anything about performance hits with certain queries or what not.

# ¿ Nov 23, 2007 09:29

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 21:38

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

I figured out my earlier problem with MySQL 5 being slow as hell - my lovely query that I designed three years ago has more parentheses and joins in it than anything should have. But while redesigning it, I had this join question:

Right now, it is (simplified) like this:

code:

SELECT e.EntryID, e.EntryText, d.DepartmentName, a.AgencyName, p.PhaseName
  FROM Entries e
       LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
       LEFT JOIN Agencies a ON d.DepartmentID = a.DepartmentID
       LEFT JOIN Phases p ON e.PhaseID = p.PhaseID

In essence, getting information about the entry, plus the name of the department it's in, plus the name of the agency within that department, plus whatever phase it's in (new, old, replaced, etc).

Again, this is vastly simplified, but my point is, would moving to commas give me the exact same results? Also, should the above be reordered, putting the Dept/Agency join at the end, or using parentheses to separate them out from the rest of the joins?

This is what I would see the above being in comma form:

code:

SELECT e.EntryID, e.EntryText, d.DepartmentName, a.AgencyName, p.PhaseName
FROM Entries e, Departments d, Agencies a, Phase p
WHERE d.DepartmentID = e.DepartmentID
  AND a.DepartmentID = d.DepartmentID
  AND p.PhaseID = e.PhaseID

Or would this mess up the joining I have in place now, which I know works? Like I said, I haven't really touched this query in a long time, but it clearly needs work done on it.

# ¿ Nov 27, 2007 13:14

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Satan's Scallion posted:

Commas are syntactically equivalent to inner join syntax, so don't get hung up on it. Have you tried running EXPLAIN on your query to see where the cost is?

Complicated looking queries are not necessarily problematic, you'll want to learn to trust EXPLAIN.

Using indexes (one of the joined tables had somehow never been indexed before) cut the execution time from interminable (10+ minutes) down to 12 seconds. Getting rid some of the joins, however, cut it down to instantaneous, so I was trying to figure out how best to clean up the web of joins. EXPLAIN is helping a lot, but I've done all I can with indexing, now I need to clean up the query itself.

If they're equivalent to an inner join, they should only be used when there's positively going to be a DepartmentID and PhaseID in Entries, right? (I apologize if some of this sounds amateur; this system has let me coast by on rather simple queries for a while, so now it's hitting me with stuff I haven't really had to think about in years)

And I actually just discovered EXPLAIN last week - it's the best thing in my life since sex. :holy:

# ¿ Nov 27, 2007 15:05

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

m5 posted:

With left joins like that, what helps is making sure you get your qualifying (filtering, in other words) criteria into the JOIN expressions. Join in an order such that your most restrictive conditions are evaluated as early as possible.

I'm not sure what you mean by getting the qualifying criteria into the join expressions... In this case, this join is simply to fill out the info on getting the actual name of the Department instead of the Department ID, etc... Now, sometimes the WHERE will be only on Entries, but sometimes it will be on the DepartmentID, but even then, it's still searching for the DepartmentID in Entries, using the one in Departments only to join the DepartmentName.

# ¿ Nov 27, 2007 15:20

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Powdered Toast Man posted:

Edit: Golbez, I have a great deal of problems due to Jet 3 being ridiculously inefficient with anything beyond a simple SELECT...especially over a network. Can I assume that using indexed fields in my queries will speed that up? I do notice that JOINs are almost always faster than a nested query...

I would say using indexed fields can help things greatly, though I don't know much outside of MySQL these days.

# ¿ Nov 28, 2007 02:05

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

fletcher posted:

So how do I read and interpret the results of the EXPLAIN to do this?

I found a great tutorial to EXPLAIN and indices at http://hackmysql.com/

Specifically, the urls http://hackmysql.com/case1 and up, just change the number.

# ¿ Dec 5, 2007 22:01

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Come join my insanity.

code:

mysql> SELECT t.Name FROM tblstuff t WHERE MATCH (t.Name,t.BriefDescription,t.Background,t.Summary,t.Status,t.Evaluation) AGAINST ("service") AND t.ID = "55";
+-----------------------------------------------------+
| Name                                                |
+-----------------------------------------------------+
| Operation and Maintenance of Video Services II      |
+-----------------------------------------------------+
1 row in set (0.00 sec)

code:

mysql> SELECT t.Name FROM tblstuff t WHERE MATCH (t.Name,t.BriefDescription,t.Background,t.Summary,t.Status,t.Evaluation) AGAINST ("services") AND t.ID = "55";
Empty set (0.00 sec)

why? why? I just chose a single result to focus on since it obviously has "Services" RIGHT THERE. When I search for 'service' I get ~4000 results. When I search for 'services', I get zero. Any ideas?

Edit: Just realized, more than 50% of the results probably contain "services". In that case, is there any way to skip the 50% limit? I suppose I could change it to boolean...

Golbez fucked around with this message at 01:25 on Dec 15, 2007

# ¿ Dec 15, 2007 01:17

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

The following search:

code:

 SELECT ... WHERE MATCH (text fields) AGAINST ('"test range"' IN BOOLEAN MODE)

will return all results with that exact phrase. So, what if I want to find 'test ranges'? Is there a way to smarten up the mysql literal string search, or do I have to hack in plurals? Note that this gives me 65 results, but if I take it out of boolean mode - so that it's looking for each word individually - I get thousands of results, because a lot of records have either test or range. Any ideas?

# ¿ Jan 7, 2008 04:26

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

imBen posted:

I don't work with MySQL but if I were writing this in SQL Server it would be
...

Yeah, I used to use LIKE %whatever% but the fulltext search is much faster and, generally, more intelligent, unless you want it to search for more than one word. (normally, the search will check for each word individually; by putting quotes around it I force it to search for the literal string, but then I'm stuck with exactly that, the literal string. Sigh. There seems to be no way, using the fulltext search, to have a fuzzy search for a string, only a fuzzy search for a word.

Is this something Lucene would be useful with?

# ¿ Jan 7, 2008 17:36

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

deimos posted:

That would make too much sense. Problem is that their documentation isn't that awesome.

I wasn't being smarmy, their documentation sucks.

It does suck a bit. I was a bit happy about 'in natural language mode' but then I saw that was the default.

What I need: It to find a literal string (i.e. more than one word) with *s on each side without the stopword or 50% limitation. Is this possible with fulltext in mysql, or should I just accept the 50% limit and/or go back to using LIKE?

Golbez fucked around with this message at 22:42 on Jan 7, 2008

# ¿ Jan 7, 2008 22:38

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Filash posted:

MySQL Fulltext Search Apostrophes Question

I'm using a MATCH AGAINST search. I want the search phrase "pete" to match the stored title "Pete's Nougat". Right now only "pete's" matches "Pete's Nougat". Is this possible?

I'm finding fulltext to be less than useful by itself. If you do it in boolean mode, you can add a * to it to include the other words - but only if you use single words! If you want to search for a literal string you can't add wildcards to it. Sigh.

Also, the problem may not be that it's not finding Pete's Nougat, but rather that "Pete" might be too short a search phrase for the fulltext. Change the "ft_min_word_len" setting; I believe the default is 4. Try that first before you try changing to a boolean search.

# ¿ Jan 14, 2008 23:48

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Filash posted:

I think fulltext has a lot of problems too. Fortunately this solution is only going to be in place for a few months.

ft_min_word_len is 3.

I still wonder if there's a way to make it so that "pete" finds "Pete's" by changing the MySQL config (and not by massaging the string somehow).

Elaborate please, are they getting rid of/drastically fixing fulltext in a few months?

# ¿ Jan 16, 2008 02:58

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Filash posted:

No, we'll just be migrating to a new solution.

Tease.

# ¿ Jan 17, 2008 21:09

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Is there any site or book that goes in to the best way to construct good search engines in PHP/MySQL? Surely fulltext isn't the end-all be-all to it... right?

# ¿ Jan 19, 2008 21:42

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

So I've given up on FULLTEXT in MySQL, it's not robust enough for my needs. I have a PHP site using a database of (at present) 13,000 entries, each with 5 text fields that need searching. The stupidity of FULLTEXT failing to find things like "testrange" and "test ranges" for the search of "test range", yet can find "test; range" has to stop, among other needed features.

I started looking (and implemented in a test environment) Lucene, via the Zend Search Lucene part of the Zend Framework, but indexing is quite slow. My plan is to use the Lucene engine to search its index for the terms, then plug those IDs into a MySQL query to get the rest of the information. But I wanted to ask about if anyone knows much about the other search engines, the main ones appear to be Sphinx and Xapian. Xapian in particular looks good, with stemming and such. Oh, and part of going to a separate indexer like this is that I will be able to index the (much more than 13,000) PDFs and DOCs on the site, files that are unsearchable in my system at present.

Any comments on Xapian or the others? Or am I going about this totally the wrong way?

# ¿ Jan 28, 2008 20:46

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Need some normalization advice: I want to save a setting if people want an email sent daily, weekly, or monthly. Would the proper style be to just keep "1", "2", and "3" in the database, and link them to another table which defines 1 as Daily, 2 as Weekly, etc.? Or would the proper style be to simply store "Daily" in the table and don't use a lookup table?

Or, should I use numbers and not even bother with a lookup table, since the possibilities will never change, and it could just be handled in code?

# ¿ Aug 5, 2008 22:05

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Stephen posted:

The general rule I stole from Coding Horrors is "Normalize until it hurts". That is to say, normalize it for now, just to make the code easy to maintain, but in the future, if your list of recipients goes huge, you may want to just reference the ID's directly to improve query performance.

I'm somewhat of a noob to normalization, so which would be the best option here?

The options:

code:

A: Use numbers, and tell the database what they mean
tblemails
  Frequency: 1, 2, or 3
tblfrequencies
  ID: 1, 2, or 3
  Name: Daily, Weekly, or Monthly

code:

B: Don't use numbers.
tblemails
  Frequency: Daily, Weekly, or Monthly

code:

C: Use numbers, but don't tell the database what they mean; handle the meaning in code, since the three possibilities will never change.
tblemails
  Frequency: 1, 2, or 3
PHP: "if 1, frequency = 'Daily'"

Which is the best practice? Right now, for simplicity, I'm going with #3...

Golbez fucked around with this message at 00:44 on Aug 6, 2008

# ¿ Aug 6, 2008 00:30

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

var1ety posted:

Nobody should ever use C. Ever.

A or B are fine. I would personally use A. Make sure you size your number column appropriately or you will reduce the benefit.

How do you mean? I figure a Tinyint would be perfectly fine.

Thanks for the advice about skipping C. I was just hoping to avoid making another table (and I see no point in storing "Daily", etc. in the main table, when a single byte can handle it), but then again, I'm sure a basic lookup table of frequencies will be useful in more than one application.

# ¿ Aug 7, 2008 00:05

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Zoracle Zed posted:

You could also do:
code:
tblemails
  Period: 1, 7, or 30
Which would give the simplicity of #3 but the flexibility (or more) of the first two options.

Zounds, that's a good idea. And then just use code to convert it to 'daily', et.al. for display purposes, but the database entry would be totally unambiguous!

Edit: Except this will run every month, rather than every 30 days. Nuts. (This is to send emails of things that have been updated in the last [x] days) I guess I could make it "31", so there might be a minor overlap but better than losing a day.

Golbez fucked around with this message at 00:10 on Aug 9, 2008

# ¿ Aug 9, 2008 00:08

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Is Toad for MYSQL (which appears to be far less expensive, that is to say, free) as good as the TOAD y'all are using for, I'm assuming, Oracle?

# ¿ Dec 23, 2008 20:14

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

It would be horribly unnormalized to do something like this, wouldn't it:

Interests: Driving = 1 Flying = 2 Gaming = 4 Movies = 8 Baseball = 16

etc etc, so someone who likes driving, gaming and baseball would have an entry of 21. I guess this would only be useful if the decoding had to be done in runtime. And even then, no.

# ¿ Feb 21, 2009 04:09

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

How should I go about (if I should) querying two databases at once? I just split out our internal user system from another database, but now I have queries that either need to look at both databases at once, or I'm going to have to do a lot of looping.

Edit: I guess it's simple, just prepend the database name, but now I'm going to have to make a single user who can view both databases...

Golbez fucked around with this message at 00:29 on Mar 3, 2009

# ¿ Mar 3, 2009 00:25

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

I'm in the market for a new job. Mostly I do PHP/MySQL stuff but I see adds for a DBA. What all does being a DBA entail? I know how to manage a MySQL server, write queries, optimize them, etc., but I'm sure there's more involved in being a DBA. What kind of things should I know?

# ¿ May 11, 2009 22:37

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

camels posted:

Data warehousing & OLAP is also a very valuable skill set to have. One more to look for is experience with ETL, importing and exporting data, scrubbing data, etc. You don't have to know data warehousing to land a DBA job, but even if a company doesn't do warehousing now, if you know what you're doing you might be able to get one started. ETL is pretty much a must-have skill; at least from my experience, I've used it in every company I've worked for.

IMO data modeling and database design is also a key skill. Depending on the job, you may be responsible for - reviewing all the DDL code; designing new database objects (tables, stored procedures, views, etc.- or even whole schemas); compiling a data dictionary; diagramming and documenting, etc.

Since I have no clue what any of these terms mean, I guess I'll pass on DBA jobs.

# ¿ May 12, 2009 02:07

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

VerySolidSnake posted:

I'd recommend against using a timestamp in MySQL, and instead use Date/Time, check out this article: http://www.dbtuna.com/article.asp?id=36

(Summarizes that date/time is about 50% faster to query, even with a larger disk space footprint)

Will datetime update automatically?

# ¿ May 19, 2009 04:05

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Triple Tech posted:

Could we put a huge note in the OP that's like stop worrying about performance. Unless your rows are measured in millions, at least... Something.

I had some queries that would take 2 minutes to execute. Often, the best optimization isn't in table structure - that's when you get into millions of rows - but in the query. Cut that motherfucker down to 1.3 seconds.

# ¿ May 20, 2009 22:46

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Dr.Khron posted:

What DBMS do you work with?

I've found that its pretty hard to fool the DB2 query optimizer... you have to start writing really ponderous, hosed up queries before it starts to effect execution time in a statistically significant way. Whenever I've tested queries overnight, the network throughput is always a much bigger variance.

I dunno, maybe this means my janitor is really good at indexing or something?

MySQL. I had queries with 20 joins and all sorts of crap that were making things horrible. A search engine rewrite later and the slow query log is so much shorter.

# ¿ May 21, 2009 02:49

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Triple Tech posted:

Or you can just be suspicious that you're doing that many joins.

Basically this. It was finally time to rip the life support off that search engine and begin anew. I also discovered the EXPLAIN command.

# ¿ May 21, 2009 04:54

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

How to run a search engine?

How I've been doing it: Query selects every column the results table needs.

How I'm toying with doing it: Query only gets the unique IDs, then passes each ID to my Record object in PHP, which then loads each ID's data.

We go from one large query, to potentially hundreds or even thousands of small ones - yet I see zero speed degradation so far (it's nice to have a dedicated database server), and things are kept inside the pretty solid class, meaning one less place where I have to deal with code. Is this a good idea to continue with, for instances where I do have a data object available? Or is this something that's going to bite me in the rear end in the future?

I almost asked this in the PHP thread but I'm guessing this is more of a MySQL question, since it's the database that's being hammered, not PHP.

# ¿ Aug 4, 2009 16:42

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Jethro posted:

There is no column called distance in your source table. distance is an alias for an expression in your select list.
code:
SELECT id,user_id,latitude,longitude,LatLonDistance(None,None,latitude,longitude) 
    AS distance 
    FROM users_userprofile 
    WHERE LatLonDistance(None,None,latitude,longitude) <= 0.5 AND personality = "Good"  
ORDER BY distance DESC

That surprises me, I thought you were able to use aliases in a WHERE clause?

:ninja:

edit: never mind,

quote:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.

# ¿ Jul 21, 2010 21:16

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

I need help.

code:

SELECT MAX(count) as total, date_year, date_month, date_day
FROM (
    SELECT COUNT(DISTINCT user_id) AS count, date_year, date_month, date_day
    FROM logs
    WHERE action = "login" 
    AND date BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
    GROUP BY date_year, date_month, date_day
) AS countquery

This is to get me the day in our database with the most logins. It gives me the most logins, but the date_ fields are always the earliest in the results, in this case 2009, 7, and 21, respectively. That's not the highest day, not by a long shot.

Can I get MySQL to tell me which day had the highest number, without doing a second query of "select date_etc from logs where count(etc) = $thenumberIjustgot"? Or doing a 'order by count desc limit 1'? I'd like to think there are more elegant methods. I don't care about tiebreakers, I'll cross that bridge when I get to it.

Golbez fucked around with this message at 23:01 on Jul 21, 2010

# ¿ Jul 21, 2010 22:54

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

I think you just need an ORDER BY in your sub query.

code:

    SELECT COUNT(DISTINCT user_id) AS count, date_year, date_month, date_day
    FROM logs
    WHERE action = "login" 
    AND date BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
    GROUP BY date_year, date_month, date_day
    ORDER BY date_year DESC, date_month DESC, date_day DESC

I might be completely wrong.

I'm not at work, but won't that order by the date, rather than the count?I mean, if I was going to resort to an order by, I'd just do it by the count and dispense with the MAX, but I was wondering if it was possible for MAX to give me the correct date.

# ¿ Jul 22, 2010 00:37

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

When you do an ORDER BY with a GROUP BY, the GROUP BY will keep the first record that the ORDER BY returns, plus calculate fields like counts. It should return the same thing as what you were getting, but with the newest dates. The whole query would look like this:
code:
SELECT MAX(count) as total, date_year, date_month, date_day
FROM (
    SELECT COUNT(DISTINCT user_id) AS count, date_year, date_month, date_day
    FROM logs
    WHERE action = "login" 
    AND date BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
    GROUP BY date_year, date_month, date_day
    ORDER BY date_year DESC, date_month DESC, date_day DESC
) AS countquery

I tried it, and it gave me the strange result of 2010, 7, 22, and ... 4? I'm running this at 9am, not 4am, so I don't know why it gave me 4... our peak hour certainly wasn't the middle of the night.

I think at this point I'll just dispense with the max and go with a simple order by. I'm still surprised there's no way to easily get the row from the max().

# ¿ Jul 22, 2010 15:27

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

You're getting a count, a year, a month and a day. Where does the hour come in?

Er, sorry, I guess I omitted that earlier, we're running hour, day, month, etc. I guess the example I pasted was day, but the one I ran was hour. But that doesn't really solve the problem of the fact that the actual max wasn't today, and definitely not at 4am.

# ¿ Jul 22, 2010 15:39

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Doctor rear end in a top hat posted:

What do the records look like when you just run the inner query?

A few examples of the day query:

code:

COUNT      date_year     date_month    date_day
-----------------------------------------------
198        2010          3             16
194        2010          3             8
183        2010          5             6

etc. So in the above set, I'd want the MAX() to just display 198, 2010, 3, 16. Right now I'm getting the same results by doing an ORDER BY COUNT, so the problem is technically solved, but I'm still curious if there's a way to get MySQL to display the row associated with MAX().

# ¿ Jul 22, 2010 15:56

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

That brings up a relational question I've had. If there were a table where there were 1000 possibilities for a book, and 50,000 entries, you would rightly have a table with a book_id and a book_name (and any other info that the book might have), and use the book_id in the main table.

But what if there is no other information, and there's only three books? Personally I was ready to make a lookup table with 3 rows and 2 columns, but they said just put the book title straight into the main table. Is this good design, bad design, neutral design, ?

# ¿ Aug 16, 2010 21:25

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Thel posted:

It's bad design and if you ever need to expand the system to cover a substantial number of books later, it's going to bite you in the rear end.

On the other hand if you know it's *never* going to expand, it's ~~faster~~lazier (in a development sense) just to dump them into the main table.

e: Tables are not resource hogs, loving use them aaaaargh.

No Safe Word posted:

Volumes being small is almost never a good reason not to use a table anyway.

Thanks for the affirmation. They wanted to keep the list of tables as clean as possible, I suppose. Thankfully it doesn't appear to be climbing beyond 3 anytime soon, nor will we need more than the title, ... though I just remembered we're also recording the chapters. Yeah. Hm. Oh well. Someday I'll convince them to rejigger it, around the same time I convince them to move on from mysql to mysqli/PDO.

Yeah.

Edit: Oh, and thanks for giving me a good line when proposing things like this, "Tables are not resource hogs, use them."

# ¿ Aug 17, 2010 14:17

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Best practices question: For a long time I've not done SELECT *, thinking it better to always specify which columns I wanted. Is this common, is there any use for SELECT *, am I making a big deal out of nothing, ?

# ¿ Aug 20, 2010 17:00

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

For the last five years, I've been doing my standard joins like so:

code:

SELECT * 
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
LEFT JOIN table3 ON table2.otherid = table3.otherid

It's been suggested I simply do...

code:

SELECT *
FROM table1, table2, table3
WHERE table1.id = table2.id
AND table2.otherid = table3.otherid

But based on my cursory research, this 'implied' join is functionally an inner join, rather than a left join. Is this correct? Though to be honest, with about 95% of my queries, the two would return the same results.

# ¿ Aug 24, 2010 20:39

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 21:38

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

Mackerel, the Thief posted:

It sounds like you work with nitpickers. I prefer writing out the joins just as you do as it's more legible, easier to understand, and easier to change.

It's literally the first time I've ever worked with other coders, and I'm the new guy, so I'm very much inclined to do what they want to fit with their style. My hope is, in a few months, I can start hopefully influencing them instead of the other way around. I'd love to introduce this joint to objects and mysqli/PDO.

# ¿ Aug 24, 2010 21:57

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›6 »