I decided to take some information I found and share it as I thought it was a very simple approach to something that many people could use.

It seems that people these days have database problems all the time and rarely know what to do about it or, even if they know there are problems, they don’t know where to start. This may help.

Generally speaking, anything after the ‘where’ clause in an SQL query needs to
have an index.


table: people::

id | name    | age | comment
0 | Billy   |  21 | I’m heavily under-caffinated.
1 | Sally   |  19 | OMG. Like, totally!
2 | Jane    |  23 | I have a pet squirrel
3 | Mark    |  30 | Whatever
4 | OldGuy  | 105 | Get off my lawn!

A simple query::

`SELECT * FROM people WHERE name = “Sally”;`

Should only return one row. If ‘name’ is indexed as it should be here (I would
use UNIQUE here), mysql only needs to retrieve the one row::

id | name    | age | comment

1 | Sally   |  19 | OMG. Like, totally!

If the column is NOT indexed, it would have to search through all 5 rows to
find the single row matching criteria. It would still only *return* one row,
but it would examine all of them

In a small table (such as this example) the speed gained by indexing may seem
negligable, but it’s when these tables grow to thousands of rows that this
becomes a problem.

Another speed trick would be to limit the number of things to grab::

`SELECT * FROM people WHERE name = “Jane” LIMIT 1;`

With the indexing mentioned above, it will still only need to pull the one row.
Without the indexing, instead of searching the entire table, it would search
long enough to find Jane, then stop, only searching 3 rows instead of 5 like
above. This would be especially useful if you use non-unique index

So why aren’t all columns indexed automatically?  There are instances where you
would never (or at least almost never) search on a specific table. In the
example given, there’s not really a common reason to ever search the ‘comment’
row, so it stays un-indexed.

Be Sociable, Share!