Tag Info

Hot answers tagged

157

Schema Changes Fetch by order --- If the code is fetching column # as the way to get the data, a change in the schema will cause the column numbers to readjust. This will mess up the application and bad things will happen. Fetch by name --- If the code is fetching column by name such as foo, and another table in the query adds a column foo, the way this ...


59

Think about what you're getting back, and how you bind those to variables in your code. Now think what happens when someone updates the table schema to add (or remove) a column, even one you're not directly using. Using select * when you're typing queries by hand is fine, not when you're writing queries for code.


33

Another concern: if it's a JOIN query and you're retrieving query results into an associative array (as could be the case in PHP), it's bug-prone. The thing is that if table foo has columns id and name if table bar has columns id and address, and in your code you are using SELECT * FROM foo JOIN bar ON foo.id = bar.id guess what happens when ...


21

Querying every column might be perfectly legitimate, in many cases. Always querying every column isn't. It's more work for your database engine, which has to go off and rummage around its internal metadata to work out which columns it needs to deal with before it can get on with the real business of actually getting the data and sending it back to you. ...


14

Yes. Always generate a new salt when the password is changed. Consider the following two scenarios: Known not changing salt I know you aren't changing the salt for your passwords. I see admin has a salt that is 'xyz', and now I grind out the rainbow table for that salt. Doesn't matter what you change your admin password to, I know it. Repeated ...


10

The short answer is: it depends on what database they use. Relational databases are optimized for extracting the data you need in a fast, reliable and atomic way. On large datasets and complex queries it's much faster and probablly safer than SELECTing * and do the equivalent of joins on the 'code' side. Key-value stores might not have such functionalities ...


7

You've misread the directionality of the quote. JOINs are generally discouraged for high-volume systems, because they are expensive (because of I/O being necessary more than one table). SO put their database into RAM specifically to avoid being hit by double disk I/O costs, and it turns out that even without physical I/O, the double table searching is ...


6

IMO, its about being explicit vs implicit. When I write code, I want it to work because I made it work, not just because all of the parts just happen to be there. If you query all records and your code works, then you'll have the tendency to move on. Later on if something changes and now your code doesn't work, its a royal pain to debug lots of queries ...


5

Don't give DROP DATABASE or DROP TABLE privileges to the DB user you use for Grails. It might also be a good idea to withhold most other privileges (other then INSERT, SELECT, DELETE) in production, as you don't want Grails to automagically do an ALTER TABLE operation that locks an important table for 30 minutes...


5

because if the table gets new columns then you get all those even when you don't need them. with varchars this can become a lot of extra data that needs to travel from the DB some DB optimizations may also extract the non fixed length records to a separate file to speed up access to the fixed length parts, using select* defeats the purpose of that


5

The server was probably started with the --safe-updates or --i-am-a-dummy option which causes MySQL to execute SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000; on startup. See the MySQL reference for details.


4

You are certainly getting close with your last thought. When you start to think about "a class could have at most ten textbooks," you're complicating the problem more than you need to. Instead of having multiple columns for the course_textbook table, stick with one for textbook and one for course. This is a many-to-many mapping, and that's exactly what you ...


4

Any sort of communication with the outside world is ultimately mediated by the operating system. There's various mechanisms available for Inter-process communication, but pipes and network sockets are probably some of the most common. If you've ever piped the output of one program into another on a command line shell, those processes were communicating with ...


3

We have been told every client will have different import layouts and different columns that identify a table's primary key. Dear lord in heaven, DO NOT let this go into production without establishing a clear standard process for giving each record of importance a single, system-wide, actual key. A key that is of the same format for all customers, ...


2

There are potential problems with storing customers together in the same database--you hit on two of them. The third that comes to mind deals with data statistics. If one customer uses the service lightly, and another heavily, it can throw off the statistics for everyone, making all the queries perform badly. I've seen that happen before (but only when the ...


1

NoSQL For your raw transactions, if the data you are getting is not required to follow a specific format, NoSQL may be a good way to store the data. If it is capable of being stored in a relational data model easily (tables and columns), there are significant speed advantages to using a relational database. How to do efficient calculations Aggregate based ...


1

Short answer: I'd suggest not to re-invent the wheel here but rather to learn from past lessons. In particular, take a look at the maildir approach. Long answer: it depends. What's your volume? Where are the bottlenecks? It's pointless to have a super-fast DB able to fetch thousands of e-mail in milliseconds, when it's going to take seconds for a user to ...


1

This sounds like you could benefit from a separation of architecture, system design, user experience and engineering. The Architects provide general guidance for the tech solution, such as "we'll use a .NET SOAP Web Service here" and "let's use an off the shelf application for this." The System Design team takes the guidance from the Architects and creates ...


1

If you don't trust the developers, I don't see how you can let them make changes to the live database. Have them change the snapshot database, review and test that, and then you apply the changes to the production database. Also, it might be worth considering whether you could reduce the number of changes you have to make to the database schema with a ...


1

I don't see any reason why you shouldn't use for the purpose it's build - retrieve all the columns from a database. I see three cases: A column is added in the database and you want it in code also. a) With * will fail with a proper message. b) Without * will work, but won't do what you expect which is pretty bad. A column is added in database and you do ...


1

We have a large (100 million row) tables which hold variable number of attributes per item too. For ad hoc querying I have created views which pivot the data i.e. turn rows into columns. The result can then be queried as if it were a "proper" table. You'll have to know the attribute names for this to work, however, or write a script which dynamically ...


1

Your best bet to improve scalability is to focus on removing calls to the database, rather than to remove data from the database. (although that may happen with things like session data) If your web application is like most I've seen, a single page load triggers multiple HTTP requests. Each of these requests is likely to trigger a session data ...



Only top voted, non community-wiki answers of a minimum length are eligible