title: Which columns should be indexed in my mySQL database?
body: <p>I am trying to design my database for a program I am working on. I want the design to be flawless before I release my program because I hear it is hard to change once it is going. To sum it up, my program is a platform to buy and sell books. Users can search within a "distance" to see if there are any books of that particular isbn within the search distance they specified. They can have the books listed by price or by date. I will describe the actions my program needs to take following the tables:</p>
  
  <pre><code>BookListings (table)...
  
      userID VARCHAR(50) NOT NULL
      dateListed timestamp Default: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      isbn13 VARCHAR(20) NOT NULL
      price UNSIGNED TINYINT NOT NULL
      email VARCHAR(30) NOT NULL
      phone VARCHAR(20) NOT NULL
      condition VARCHAR(30) NOT NULL
      latitude FLOAT(9,7) NOT NULL
      longitude FLOAT(9,7) NOT NULL
  
  ContactInfo (table)...
  
     email VARCHAR(30) NOT NULL
     phone VARCHAR(20) NOT NULL
  </code></pre>
  
  <p>Keep in mind ContactInfo table is not really of much importance. I am going to clear it every so often once I move the information off of the database to free up space. If this table is causing major efficiency problems, I could sacrifice it completely and I wouldn't really be bothered.</p>
  
  <p><strong>BannedUsers</strong> (table)...</p>
  
  <ul>
  <li><strong>userID</strong> <em>VARCHAR(50)</em> NOT NULL</li>
  <li><strong>banReason</strong> <em>VARCHAR(50)</em> NOT NULL</li>
  </ul>
  
  <p>BannedUsers table is where I am going to keep track of bans. It will rarely be used but if for some reason I want to disable someone from using my program I can just put their userID in there. When my program starts, it checks the BannedUsers table to see if user is banned and if so it gives them the ban reason.</p>
  
  <p>I am kind of confused where to place indexes (I don't know much about them). I just hear that indexes speed up searches immensely. For my BannedUsers I think it is obvious to put index on userID column? If so, what kind of index?</p>
  
  <p>For BookListings table it is more confusing to me where to place indexes. First I will explain all of the features of my program (actions to be taken) along with the query taken from my php script:</p>
  
  <ol>
  <li>I use the following query to enter a listing into my system. This is for when a user wants to sell a book, they "list" the book. So you can imagine this action will be done quite a bit...</li>
  </ol>
  
  <p></p>
  
  <pre><code>INSERT INTO Listings
  VALUES ('$userID', (NOW() + INTERVAL 2 HOUR), '$isbn13', $price, '$email', '$phone', 
          '$condition', '$latitude', '$longitude')
  </code></pre>
  
  <ol start="2">
  <li>I use the following query when the user wants to buy (search) for a book listing within a certain distance from them. Just like the query to list a book, this action will also be used a lot:</li>
  </ol>
  
  <p></p>
  
  <pre><code>SELECT
      *, (
        6371 * acos (
        cos ( radians($userLatitude) )
        * cos( radians( latitude ) )
        * cos( radians( longitude ) - radians($userLongitude) )
        + sin ( radians($userLatitude) )
        * sin( radians( latitude ) )
      )
  ) AS distance
  FROM Listings
  WHERE isbn13='$isbn13'
  HAVING distance &lt;= $withinDistance
  ORDER BY price, dateListed
  </code></pre>
  
  <p>The above query is to order by the price. The following query orders by the date instead:</p>
  
  <p></p>
  
  <pre><code>SELECT
      *, (
        6371 * acos (
        cos ( radians($userLatitude) )
        * cos( radians( latitude ) )
        * cos( radians( longitude ) - radians($userLongitude) )
        + sin ( radians($userLatitude) )
        * sin( radians( latitude ) )
      )
  ) AS distance
  FROM Listings
  WHERE isbn13='$isbn13'
  HAVING distance &lt;= $withinDistance
  ORDER BY dateListed DESC
  </code></pre>
  
  <ol start="3">
  <li>The following query is done right after listing a book. It is a way to collect user contact information:</li>
  </ol>
  
  <p></p>
  
  <pre><code>INSERT INTO ContactInfo
  VALUES ('$email', '$phone')
  </code></pre>
  
  <ol start="4">
  <li>The following query is used at the beginning of the program to find out how many books are listed in the system (just to keep a fun count of how many books the system has dealt with, not really that important):</li>
  </ol>
  
  <p></p>
  
  <pre><code>SELECT COUNT(*) FROM ContactInfo
  </code></pre>
  
  <ol start="5">
  <li>The following query is used to find all of the user's books that they have listed in the system. It will be done quite often because they need to do it before they delete a listing they made. It is basically used to show them all of their listings, then they choose which one to delete:</li>
  </ol>
  
  <p></p>
  
  <pre><code>SELECT dateListed, isbn13, price 
  FROM Listings 
  WHERE userID='$userID' 
  ORDER BY dateListed DESC
  </code></pre>
  
  <ol start="6">
  <li>Here is the query where they actually deleted the listing:</li>
  </ol>
  
  <p></p>
  
  <pre><code>DELETE FROM Listings WHERE userID='$userID' AND isbn13='$isbn13
  </code></pre>
  
  <p>Please help me make my design efficient. I am not so sure where to index because I am aware that with indexing means it makes updating and deleting harder.. which my program also needs to do. Initially I thought of indexing isbn13 (the main thing that will be searched upon), but then realized that I will also be searching upon the latitude and longitude so I am not sure if those also have to be indexed... it is really confusing me. Please tell me anything I can do to improve the design and queries of my database.</p>