Simple "Select" - but better?

[TheFilmGod]

I know how to do a simple select.

 

Where you use "SELECT * FROM attendance WHERE 'id' = '1'"

 

Something like that...

 

Anyway, is there a way to do the same select without the use of " * ". What's the most efficient way of doing this?

 

.........

 

Last question, what is the idea of using indexes? Is it simply used to get relevant data from an id. Like an article with its comment ids and then those ids are used to find the comments from a different table? Is that the idea of using indexes?

[svivian]

If the id is unique (or you're only ever going to want one entry) you can add "LIMIT 1" to the end of the query. This means that MySQL will stop looking for matches after it finds one.

 

Also if your id field is an INT (which it ought to be, rather than any text-based field eg varchar) when you don't need the apostrophes around '1':

SELECT * FROM attendance WHERE `id` = 1 LIMIT 1

 

Regarding the *, you can select just the fields you need by naming them, comma-separated, eg:

SELECT field1, field2, field3 FROM attendance WHERE `id`=1 LIMIT 1

Even if you're selecting all the fields this is not a bad idea because if you add a bunch of fields to the table in the future you won't automatically be selecting them unnecessarily.

 

And indexes are used to speed up queries. I don't know all the details, but essentially some data is stored separately, like an index in a book. It's a good idea to index fields that are often in the 'WHERE' part of your queries, like your id field and fields you are searching on.

[fenway]

I know how to do a simple select.

 

Where you use "SELECT * FROM attendance WHERE 'id' = '1'"

 

Something like that...

 

Anyway, is there a way to do the same select without the use of " * ". What's the most efficient way of doing this?

 

.........

 

Last question, what is the idea of using indexes? Is it simply used to get relevant data from an id. Like an article with its comment ids and then those ids are used to find the comments from a different table? Is that the idea of using indexes?

 

Not sure what you mean by "without the *" -- either you want all of the fields or you don't.

 

As for indexes, read this first.

[TheFilmGod]

That helped a lot! But, I'm still confused.

 

How do I create an index? What if I want a column named "id" that is auto_increment and is an index?

 

How do I code that?

 

Finally, can things like "last name" or "first name" be an index? - or does it have to be a number?

[fenway]

That helped a lot! But, I'm still confused.

 

How do I create an index? What if I want a column named "id" that is auto_increment and is an index?

 

How do I code that?

 

Finally, can things like "last name" or "first name" be an index? - or does it have to be a number?

You can create an index with CREATE INDEX, or as part of the CREATE TABLE syntax.

 

You can specify a certain column as the "primary key" and make it auto-increment.

 

You can have an index on just about anything.

 

Again, the document that I referred you to should have addressed these issues with examples.

[TheFilmGod]

You can have an index on just about anything.

 

Really? - it doesn't make any sense! I understand auto_increment numbers but how does mysql know if "bob" is already an index and exactly where it is? Wouldn't it first have to read the whole index file?

[fenway]

It's no different (in principle) that an index at the back of a book... you simply keep track of where things are.  And yes, it will always have to access the index file -- it's not different for numbers, either.  It's a glorified hash lookup (although the implementation is much more involved), like a bucket sort.