General MySQL Questions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

General MySQL Questions

Post by icesolid »

I was just wondering if these methods are the best to use:

#1. For boolean fields I used tinyint(1) (0 being false and 1 being true). I use that for true/false. Is that the best field type to use?

#2. When calling up a SELECT query, if I only need for ex: customer,amount,gross fields from the database, should I define them or is using * (select all) ok?

ex: is it better to do it this way:

Code: Select all

SELECT customer,amount,gross FROM cases
or this way:

Code: Select all

SELECT * FROM cases
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

For #1, that's what I do, and I've yet to see anything better.

For #2, if I want specific data returned via a query, I ask for it, so I go with

Code: Select all

SELECT `column1`, `column2`
FROM `table`
This has the benefit of you knowing exactly what information is coming back and what's supposed to be in each column, and if you're querying remote databases, cuts down on superflous data transmission.

Now, if I'm sitting right at the server and doing some quick and dirty ad hoc querying, I'll do SELECT * just 'cause it's easier. But never in a script.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

I agree with the Rev.

FYI: MySQL kind of has a BOOLEAN type, so you can use that in your CREATE TABLE... queries. Of course, MySQL will convert it to tinyint(1) just like you did.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Post by icesolid »

Does using ` ` around table and column names help or optimize the queries at all?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

It prevents possible name collisions of current and future reserved words
Post Reply