Mysql Select from statement

[LanceT]

If we have a massive table named massive, lets say with 100,000 rows, and we are trying to find one row where hello = $hello, how intensive would it be to use the below query?

 

mysql_query("SELECT * FROM massive WHERE hello = '$hello') 

 

Does this command take up a lot of system memory? Are there any ways to decrease the amount of system usage? Will it slow down the whole computer?

 

 

[hvle]

it will be extremely intensive if you do not index the 'hello' column.

however if you do, and you must, it will have log(n) performance.

 

 

[LanceT]

it will be extremely intensive if you do not index the 'hello' column.

however if you do, and you must, it will have log(n) performance.

 

how do I index the 'hello' column?

 

What is log(n) mean?

 

Also what alternatives are there to using the select statement?

[hvle]

how do I index the 'hello' column?

Read the Mysql manual on 'Indexing'

 

What is log(n) mean?

if you have N records on your table, and do not index the column you wanted to search, mysql will go 1 by 1 searching.  So it will check all N records.  If you indexed, mysql automaticly sort the records, and perform binary search which is much faster.

 

say if you have 1024 records:

with no Index:  1024 comparisons

with index: 10 comparisons

 

say if 1 comparison cost 10 ms, with 1024 records:

with index: 0.1 second

with no index: 10 seconds

 

see how much time you saved?

 

Also what alternatives are there to using the select statement?

none

[LanceT]

how do I index the 'hello' column?

Read the Mysql manual on 'Indexing'

 

 

Can someone provide a link to this, I can't seem to find it on Google. Also, do you think that an index can be used for pretty much any situation when using the SELECT from statement?

 

Thanks.

 

[shoz]

how do I index the 'hello' column?

Read the Mysql manual on 'Indexing'

 

 

Can someone provide a link to this, I can't seem to find it on Google. Also, do you think that an index can be used for pretty much any situation when using the SELECT from statement?

 

Thanks.

 

 

http://dev.mysql.com/doc/refman/5.0/en/indexes.html

 

The doc should explain in what instances indexes are useful. Btw, don't forget to click "Next".

[LanceT]

since i'm quite new to mysql, it might take me a while to understand the indexing thing. Is it possible that I make my script now and then configure the indexing or will that be too hard?

[shoz]

since i'm quite new to mysql, it might take me a while to understand the indexing thing. Is it possible that I make my script now and then configure the indexing or will that be too hard?

 

You can add the indexes later on if you'd like.

[LanceT]

will the index work with string values?

[hvle]

will the index work with string values?

Yes, it called Full-text indexing.  You can add it later at any time

[LanceT]

alright i've been reading the guide and I am thoroughly confused. Anyone know of any other guides I can look through? Or step by step instructions on how to set up my index and how I would need to change my code.

[shoz]

You can add an index using the following syntax

 

ALTER TABLE tablename ADD INDEX(columnname)

 

Optimization Tutorial

http://hackmysql.com/optimize

[LanceT]

You can add an index using the following syntax

 

ALTER TABLE tablename ADD INDEX(columnname)

 

Optimization Tutorial

http://hackmysql.com/optimize

 

Is that the only thing I have to do to add an index and then everything will run faster? Or is there more stuff involved.

 

Can someone explain what adding an index does basically and how to implement a select from statement?

 

 

[LanceT]

alright this is what i did

 

ALTER TABLE `tablename` ADD FULLTEXT (
`columnname`
)

 

Now, am I set with an index (meaning my script will run faster) or is there other stuff I have to do?

[shoz]

alright this is what i did

 

ALTER TABLE `tablename` ADD FULLTEXT (
`columnname`
)

 

Now, am I set with an index (meaning my script will run faster) or is there other stuff I have to do?

 

That depends on the query being used. If you have an actual issue that you need help with you'll need to explain the problem in detail.

[LanceT]

alright this is what i did

 

ALTER TABLE `tablename` ADD FULLTEXT (
`columnname`
)

 

Now, am I set with an index (meaning my script will run faster) or is there other stuff I have to do?

 

 

 

That depends on the query being used. If you have an actual issue that you need help with you'll need to explain the problem in detail.

 

well like I said in my original post, I am using a select from statement

 

mysql_query("SELECT * FROM tablename WHERE columnname = '$column') 

 

Now that I've indexed the table (using fulltext) does that mean mysql queries will run faster? Is there anything else I have to implement? Consider that this table is huge (over 10000 entries).

 

Also are there any drawbacks to using indexing? If not, why doesn't everyone just index everything?

 

 

[shoz]

The query you posted does not make use of a FULLTEXT index. The Mysql docs give detailed instruction on what is required to use that particular index.

 

By "Explain in Detail" I mean that you need to explain what you're actually trying to do. If you have a query that you need help with you'll need to post the version of mysql you're using

 

SELECT VERSION();

 

the create statement for the table

SHOW CREATE TABLE tablename

 

and a brief explanation of what the variable "$column" should contain and what you'd like to retrieve.

 

Also are there any drawbacks to using indexing? If not, why doesn't everyone just index everything?

 

One drawback is that Indexes slow down INSERT and UPDATE operations as the index will need to be updated along with the table data.

[LanceT]

Awesome, I've learned quite a bit from you shoz. Here's the details.

 

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` char(30) NOT NULL,
  `password` char(30) NOT NULL,
  `active` char(5) NOT NULL,
  `lastlogin` datetime NOT NULL,
  `name` char(30) NOT NULL,
  `last_name` char(30) NOT NULL,
  `email` char(30) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`),
  KEY `password` (`password`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

 

That is my create table. It is supposed to hold some fields of a user.

 

Mysql version is 5.0.16.

 

My code that im using in PHP uses the select from statement to find data from the inputted username. So once they input the username, they can get e-mail, first name, last name, etc.

 

Creating an index for username and password should help me to lessen my server load?

 

Thanks.

 

 

[shoz]

Awesome, I've learned quite a bit from you shoz. Here's the details.

 

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` char(30) NOT NULL,
  `password` char(30) NOT NULL,
  `active` char(5) NOT NULL,
  `lastlogin` datetime NOT NULL,
  `name` char(30) NOT NULL,
  `last_name` char(30) NOT NULL,
  `email` char(30) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`),
  KEY `password` (`password`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

 

That is my create table. It is supposed to hold some fields of a user.

 

Mysql version is 5.0.16.

 

My code that im using in PHP uses the select from statement to find data from the inputted username. So once they input the username, they can get e-mail, first name, last name, etc.

 

Creating an index for username and password should help me to lessen my server load?

 

Thanks.

 

MYSQL will have much less work to do with the index on the username column. The index should be UNIQUE in this case as 2 usernames (I assume) should never be the same.

 

ALTER TABLE tablename ADD UNIQUE(columnname)

 

A fulltext index isn't required because you're looking for exact matches.

[LanceT]

alright so I added an index for username, also a unique for username. Will this help my script to be faster?

 

Also what if I added an index for password (since some queries will check both username and password)? Will that also help me to speed up my script.

 

Thanks a lot.

[shoz]

alright so I added an index for username, also a unique for username. Will this help my script to be faster?

 

You should either have the UNIQUE or the normal INDEX on username not both. Assuming nothing else is happening in the script to cause it to slow down the script should be faster because of the index.

 

Also what if I added an index for password (since some queries will check both username and password)? Will that also help me to speed up my script.

 

If you'll be checking both username and password a multi-coumn UNIQUE index on both columns would be the most appropriate. Keep in mind however that the performance increase will most likely be unnoticeable.

 

ADD UNIQUE(username, password)

 

Again, that should be the only index you add. ie there shouldn't be a separate index on username or password alone.

[LanceT]

Ah yes, I just realized that my mySQL had an error when I set both an index and a unique.

 

Does setting my password column to be unique mean that users can't share the same password?

 

Also from reading other places, people said adding a unique index will help my script to considerably run faster. So you say that it actually won't be noticeable?

 

[shoz]

Ah yes, I just realized that my mySQL had an error when I set both an index and a unique.

 

Does setting my password column to be unique mean that users can't share the same password?

 

If you put a UNIQUE index on the password column alone then two users won't be able to share the same password. Putting a UNIQUE multi-column index on "username" and "password" won't allow two users to have the same combination of username and password.

 

Now that I look at it again, to keep the enforcement of unique usernames you'll actually need a UNIQUE index on the username column and if a multi-column index is put on (username, password) it can be, but doesn't need to be UNIQUE. If the only unique index on the table was (username, password) it would still allow for users to have the same username.

 

Also from reading other places, people said adding a unique index will help my script to considerably run faster. So you say that it actually won't be noticeable?

 

 

The reason why I said that you would not likely see a difference in having a UNIQUE index on (username, password) rather than one only on (username) is because if your query is already using a unique identifier to find the row it doesn't matter much to have a different UNIQUE index that will serve the same purpose in the context of the query. MYSQL will use either UNIQUE index to find the row and the other column data in the same way.

 

Now that it's clear that a UNIQUE index should be on the username column to ensure duplicate usernames aren't entered into the table, the multi-column index on the username and password columns should be DROPed as there should only be as many indexes as needed.

[LanceT]

ok I'm learning more and more. Thanks a lot shoz.

 

So the unique I have set for "id" pretty much negates all the benefits of having a unique index for "username"? Even though my select from statements pulls the "username" and not the id?

 

Since I've set a unique for id and username now, would it benefit me at all to setup an id for "email" if another select from statement checks for e-mail?

 

Thanks.

 

[shoz]

it doesn't matter much to have a different UNIQUE index that will serve the same purpose in the context of the query

 

In the context of the query that you're using it does matter whether or not you have an additional UNIQUE index on username because username is the field being used in the comparison and id is not.

 

It doesn't matter if you have a UNIQUE index on (username , password) if you already have a UNIQUE index on username because the query already is looking for a match on a column that's uniquely indexed.

 

Btw, these are not hard and fast rules.  Based on what you're doing this would be the approach I'd take.

 

EDIT: Note that the explanation is from the standpoint of performance. Again, the username column should also be uniquely indexed to ensure no two users share the same username.