 |
 |
 |
 |
|
|
|
Best Practices: Database Indexing
SELECT * FROM mytable WHERE category_id=1;
Even the most rudimentary indexing strategy means you will create a simple
index on category_id:
CREATE INDEX mytable_categoryid ON mytable (category_id);
Very simple right? Now what if you are going to use multiple criteria to select
from the database?
SELECT * FROM mytable WHERE category_id=1 AND user_id=2;
You might be tempted to just create another index on user_id, but that
would be sub-optimal. You need to create a multi-column index.
CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);
Are you noticing my naming convention yet? I use tablename_field1name_field2name.
You'll understand why I do that in a minute.
Now that you've built indices on the appropriate columns, you should verify
that your database is using them as expected. This is easy to do in most databases -
just use the EXPLAIN command:
EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2;
This is what Postgres 7.1 returns (exactly as I expected).
NOTICE: QUERY PLAN: Index Scan using mytable_categoryid_userid on mytable (cost=0.00..2.02 rows=1 width=16) EXPLAIN
If you boil that down, essentially postgres is using an index (always a
good start), and furthermore it is using the second index I created.
Because of the way I named my index, I can tell immediately that it is
using the right index.
Now what if your query involves an ORDER BY clause? Believe it or not,
most databases will take advantage of an index on the ordered by field.
SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY adddate DESC;
Confused yet? Don't be. Add another multi-column index that includes
the fields in the WHERE clause as well as the ORDER BY clause and you're
in business:
[ Next Page ]
| Comments: | ||
| How does indexing work ? | Andy | 09/30/07 18:56 |
| Large Database with frequent inserts and upda | Krish | 05/19/05 08:03 |
| RE: So what are indexes? | Kenny G. | 04/29/04 19:56 |
| Nice article! | Fabien Papleux | 08/06/03 14:49 |
| php pgm in doubt | pravin | 01/27/03 05:55 |
| RE: Damaged Indexes | devlin | 12/13/02 17:48 |
| So what are indexes? | Amos | 12/05/02 17:42 |
| Damaged Indexes | George Esoimeme | 10/16/02 05:10 |
| question | madEr | 09/10/02 06:38 |
| indexing and optimlzation | Michel Schellekens | 08/12/02 06:05 |
| RE: how to search 1 table with 350 columns ? | Dan Reiland | 07/09/02 13:53 |
| RE: WHERE col LIKE '%df%' | Tim | 07/08/02 07:42 |
| Dyanmic WHERE clauses | Jonathan Chum | 06/06/02 14:38 |
| RE: mysql speed | Son Nguyen | 05/18/02 17:22 |
| WHERE col LIKE '%df%' | A Martin | 05/15/02 18:26 |
| how to search 1 table with 350 columns ? | Pietro | 05/08/02 06:51 |
| Creating Index for Simple MySQL Database | mKarston | 04/23/02 11:57 |
| mysql speed | udayk | 04/07/02 03:27 |
| Postgres optimization - hints needed | Per | 03/22/02 03:04 |
| Indexes | Louise | 03/18/02 14:54 |
| Databases Indexing | Huilee | 03/07/02 21:29 |
| winmysql connection problem. | Jong | 03/04/02 00:00 |
| Foreign Keys - indexed by default ? | Sanjeev Deora | 02/26/02 15:41 |
| What about selects with OR instead of AND | Ryan Barnett | 01/22/02 10:46 |
| example | J. M. Weggemans | 01/22/02 09:24 |
| RE: K-Sensitive login | Bob | 01/21/02 08:59 |
| Re: Indexing - what if I add a row? | James Puddicombe | 11/10/01 05:03 |
| Indexing - what if I add a row? | Jay Vincent | 11/08/01 11:23 |
| RE: Optimization musings-comment threads | syco | 11/05/01 11:57 |
| RE: K-Sensitive login | richard | 09/24/01 17:42 |
| K-Sensitive login | Storsh | 09/12/01 04:53 |
| RE: EXPLAIN: MySQL vs. Postgres | Patrick Massey | 06/25/01 13:44 |
| EXPLAIN: MySQL vs. Postgres | Vincent Driessen | 05/06/01 15:06 |
| RE: Newbie needs a litlle help | JimmiZ | 04/16/01 11:17 |
| RE: Optimization musings-comment threads | Boon | 03/28/01 23:27 |
| RE: Newbie needs a litlle help | Ken Robson | 03/28/01 14:11 |
| Newbie needs a litlle help | Maarten Verheijen | 03/25/01 12:04 |
| Knowing whether mysql used your index | greg | 03/17/01 16:39 |
| Indexing a JOIN | Nathan | 03/13/01 03:57 |
| RE: Optimization musings-comment threads | Phillip Oertel | 03/02/01 15:06 |
| RE: LEFT JOINs under MySQL | rod k | 02/15/01 19:15 |
| LEFT JOINs under MySQL | spikeymikey | 02/15/01 16:54 |
| Multiple ors in sql statement | Wes | 02/09/01 13:05 |
| RE: multi-column indices and MySQL - Tim | rod k | 02/09/01 07:05 |
| RE: multi-column indices and MySQL - Tim | Ben Schumacher | 02/08/01 13:13 |
| pgsql: CLUSTER command | Terence | 02/04/01 01:23 |
| RE: comment threads - and other gripes | Terence | 02/04/01 01:08 |
| RE: Yay indices | Yaron Yogev | 02/03/01 22:34 |
| Yay indices | Anna | 01/31/01 18:37 |
| RE: The how is well explained but why? | doug mackenzie | 01/30/01 21:43 |
| RE: multi-column indices and MySQL | Don Baccus | 01/26/01 21:13 |
| RE: Optimization musings-comment threads | Glenn | 01/22/01 23:06 |
| Prev - Next article links | Will | 01/22/01 08:22 |
| RE: order of fields | Anna | 01/19/01 14:37 |
| RE: Optimization musings-comment threads | Scott Molinari | 01/19/01 13:16 |
| RE: order of fields | Chris Newman | 01/19/01 10:21 |
| order of fields | Anna | 01/19/01 00:16 |
| RE: The how is well explained but why? | Anna | 01/18/01 20:28 |
| RE: Optimization musings-comment threads | Tim Perdue, PHPBuilder.com | 01/18/01 13:39 |
| RE: Optimization musings-comment threads | Scott Molinari | 01/18/01 13:04 |
| The how is well explained but why? | Scott Molinari | 01/18/01 12:40 |
| RE: multi-column indices and MySQL - Tim | Alan L | 01/15/01 09:34 |
| cardinality | Yaron Yogev | 01/14/01 09:23 |
| RE: Index: What for ? | Yaron Yogev | 01/14/01 09:01 |
| RE: Optimization musings | Brian | 01/13/01 20:10 |
| RE: primary key == index ? | Baruch Even | 01/12/01 16:36 |
| primary key == index ? | juozas salna | 01/12/01 13:18 |
| RE: You need to know your data | Chris Newman | 01/12/01 07:29 |
| RE: Optimization musings | Baruch Even | 01/11/01 16:51 |
| RE: Optimization musings | Kirk Parker | 01/11/01 15:07 |
| RE: Optimization musings | Baruch Even | 01/11/01 12:49 |
| RE: Optimization musings | Kirk Parker | 01/11/01 00:40 |
| RE: What about MySQL? - Rod | Brian | 01/10/01 19:10 |
| MySQL vs. Postgres | Jeremy Rempel | 01/10/01 19:05 |
| RE: multi-column indices and MySQL - Tim | Tim Perdue, PHPBuilder.com | 01/10/01 17:37 |
| RE: What about MySQL? - Brian | rod k | 01/10/01 17:31 |
| RE: multi-column indices and MySQL - Tim | rod k | 01/10/01 17:29 |
| RE: Optimization musings | Brian | 01/10/01 12:52 |
| Index: What for ? | Anderson Fortaleza | 01/10/01 12:48 |
| Optimization musings | Baruch Even | 01/10/01 06:31 |
| RE: Order descending without sorting? | Andrew Coldham | 01/10/01 02:34 |
| RE: What about MySQL? | curt | 01/10/01 00:33 |
| RE: What about MySQL? | Tim Perdue, PHPBuilder.com | 01/09/01 20:34 |
| Order descending without sorting? | Mats | 01/09/01 19:14 |
| RE: What about MySQL? | curt | 01/09/01 17:41 |
| RE: A little caution | Björn Brändewall | 01/09/01 16:34 |
| You need to know your data | Frank Feingold | 01/09/01 12:37 |
| What about MySQL? | Brian | 01/09/01 12:06 |
| RE: um... | Paul K Egell-Johnsen | 01/09/01 11:07 |
| um... | Chris Snyder | 01/09/01 10:05 |
| RE: A little caution | Tim Perdue, PHPBuilder.com | 01/09/01 05:57 |
| Confused one.. | Arni | 01/09/01 03:57 |
| Indexes can be temporary | Peter Moulding | 01/09/01 02:26 |
| RE: A little caution | Kirk Parker | 01/09/01 00:39 |
| RE: multi-column indices and MySQL | Tim Perdue, PHPBuilder.com | 01/08/01 23:00 |
| RE: hmm... | Tim Perdue, PHPBuilder.com | 01/08/01 22:59 |
| RE: A little caution | Tim Perdue, PHPBuilder.com | 01/08/01 22:57 |
| multi-column indices and MySQL | rod k | 01/08/01 22:50 |
| A little caution | Kirk Parker | 01/08/01 22:27 |
| hmm... | Andrew | 01/08/01 22:12 |
|
If you are looking for help, please post on the appropriate forum here. Your questions will be answered much more quickly. | ||
