{"id":1527,"date":"2018-01-19T19:22:56","date_gmt":"2018-01-19T19:22:56","guid":{"rendered":"http:\/\/goofy-trucks.flywheelsites.com\/best-practices-database-indexing\/"},"modified":"2018-01-19T19:24:51","modified_gmt":"2018-01-19T19:24:51","slug":"best-practices-database-indexing","status":"publish","type":"post","link":"https:\/\/phpbuilder.com\/best-practices-database-indexing\/","title":{"rendered":"Best Practices: Database Indexing"},"content":{"rendered":"
\n
\n
By Tim Perdue<\/div>\n
on January 8, 2001<\/div>\n<\/p><\/div>\n
\"picture<\/p>\n
\nLike most PHP developers out there, I started off using databases to
\nstore relatively simple data structures for my dynamic sites. PHP’s
\nslickness and ease of database connectivity is no doubt a major reason
\nfor its wild success, and also probably a reason why databases get
\ncreated and used without a huge amount of design and documentation.<\/div>\n
\nIn my next couple of articles, I’m going to attempt to talk about
\nthe best way to use databases with
\nPHP. I’m not going to cover normalization, as Barry Wise wrote a
\nfantastic article<\/a> on that topic.<\/div>\n
\nInstead, I’m going to cover 3 other, arguably less important, advanced
\ntopics:<\/div>\n
\n
    \n
  1. Proper use of indices (indexes) – this article<\/li>\n
  2. Transactions – next week<\/li>\n
  3. Foreign Keys – two weeks<\/li>\n<\/ol>\n<\/div>\n

    Indexing<\/h2>\n
    \nLet’s start with indexing. I was asked the other day by a fairly advanced
    \nprogrammer what indices are, and that scared me. I knew I had to write
    \nup an article as this programmer is clearly not alone. Tens of thousands of
    \ndevelopers (most probably using MySQL) have little formal training with
    \ndatabases and yet make a living developing on them for clients.<\/div>\n
    \nThe very first thing you should do, if you do nothing more, is build an
    \nindex on any column in a table that is included in a WHERE clause.<\/div>\n
    \n
    \nCREATE TABLE mytable (\nid serial primary key,\ncategory_id int not null default 0,\nuser_id int not null default 0,\nadddate int not null default 0\n);\n<\/pre>\n<\/div>\n
    \nThat’s a pretty simple table that will suffice for most of this discussion.\n<\/div>\n<\/div>\n
    <\/p>\n
    \n
    1<\/div>\n
    | <\/div>\n
    2<\/a> <\/div>\n
    | <\/div>\n
    3<\/a> <\/div>\n
    Next Page \u00bb<\/a><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"
    By Tim Perdue on January 8, 2001 Like most PHP developers out there, I started off using databases to store relatively simple data structures for my dynamic sites. PHP’s slickness and ease of database connectivity is no doubt a major reason for its wild success, and also probably a reason… <\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1527","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1527","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/comments?post=1527"}],"version-history":[{"count":1,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1527\/revisions"}],"predecessor-version":[{"id":2271,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1527\/revisions\/2271"}],"wp:attachment":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/media?parent=1527"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/categories?post=1527"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/tags?post=1527"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}