Which columns should be indexed in my mySQL database?
database, database-design, mysql
Solution
There's no free lunch. Indexes have benefits as well as costs.
The benefit is that some operations will be faster.
The cost is that some operations will be slower, and you will consume more disk space and memory.
Finding records (including finding them for updates and deletes) will be faster, but updating them, deleting them, and inserting them will be slower, as the index needs to be updated.
Are your queries currently slow? Why? You need to look at your execution plans to see why they are slow. If they are slow due to sequential scans, then try adding an index. How does this affect inserts, deletes and updates? Is it worth the cost? Do you have sufficient disk space and memory for these indexes? These are questions we cannot answer for you.
Problem
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: ``` 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 ``` 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. BannedUsers (table)... - userID VARCHAR(50) NOT NULL - banReason VARCHAR(50) NOT NULL 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. 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? 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: - 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... ``` INSERT INTO Listings VALUES ('$userID', (NOW() + INTERVAL 2 HOUR), '$isbn13', $price, '$email', '$phone', '$condition', '$latitude', '$longitude') ``` - 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: ``` 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 <= $withinDistance ORDER BY price, dateListed ``` The above query is to order by the price. The following query orders by the date instead: ``` 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 <= $withinDistance ORDER BY dateListed DESC ``` - The following query is done right after listing a book. It is a way to collect user contact information: ``` INSERT INTO ContactInfo VALUES ('$email', '$phone') ``` - 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): ``` SELECT COUNT(*) FROM ContactInfo ``` - 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: ``` SELECT dateListed, isbn13, price FROM Listings WHERE userID='$userID' ORDER BY dateListed DESC ``` - Here is the query where they actually deleted the listing: ``` DELETE FROM Listings WHERE userID='$userID' AND isbn13='$isbn13 ``` 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.