How to optimise below query?

Question

SELECT r.*, u.username 
FROM `reservation` AS r JOIN 
     `users` AS u 
WHERE u.id = r.user_id 
AND   DATE(r.bx_date) >= DATE('2012-08-22') 
AND   DATE(r.bx_date) <= DATE('2012-08-22') 
AND   r.status='1' 
ORDER BY r.id desc

bx_date - booked date.

It takes more than 8 secs to run this query. I have more than 500,000 records in reservation table and 40,000 records in users table.

I have not done any optimization to my database tables. Nothing at all. Just PKs only. How can I optimize this query ? What are the options to increase the performance of this database.

Thanks

Table reservation:

CREATE TABLE `reservation` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(10) unsigned NOT NULL COMMENT 'User ID',
 `tx_id` varchar(15) NOT NULL COMMENT 'Transaction ID',
 `tx_date` datetime NOT NULL COMMENT 'Transaction Date',
 `bx_date` date NOT NULL COMMENT 'Booking Date',
 `theater_id` int(10) unsigned NOT NULL COMMENT 'Theater ID',
 `movie_id` int(10) unsigned NOT NULL COMMENT 'Movie ID',
 `showtime_id` int(10) unsigned NOT NULL COMMENT 'Show Time ID',
 `category_id` int(10) unsigned NOT NULL COMMENT 'Category ID',
 `full_tickets` tinyint(2) unsigned NOT NULL COMMENT 'Number of Full Tickets',
 `half_tickets` tinyint(2) unsigned NOT NULL COMMENT 'Number of Half Tickets',
 `no_seats` tinyint(3) unsigned NOT NULL COMMENT 'No of Seats',
 `full_ticket_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Full Ticket Price',
 `half_ticket_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Half Ticket Price',
 `amount` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Total Amount',
 `method` tinyint(1) unsigned NOT NULL COMMENT 'Payment Method 1=web 2=mobile 3=theater',
 `paymentgateway_id` int(10) unsigned NOT NULL COMMENT 'Payment Gateway ID',
 `payment_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0 = Cash 1=Credit Card',
 `status` tinyint(1) unsigned NOT NULL COMMENT 'Status 0=provisional 1=booked 2=canceled 3=auto canceld',
 `comment` text,
 `reservation_type` tinyint(1) unsigned NOT NULL COMMENT 'Reservation Type 0=complimentary 1=advamce booking 2=Mobile Booking, 3 - Theater Offline Booking',
 `complimentary_type` tinyint(1) unsigned NOT NULL COMMENT '0=none 1=loyalty rewards 2=marketing 3=promotional 4=show blocking 5=vip',
 `description` mediumtext NOT NULL COMMENT 'Complimentary Description',
 `title` varchar(10) DEFAULT NULL COMMENT 'Title',
 `fname` varchar(255) DEFAULT NULL COMMENT 'First Name',
 `lname` varchar(255) DEFAULT NULL COMMENT 'Last Name',
 `gender` tinyint(1) unsigned DEFAULT NULL COMMENT 'Gender 0=male 1=female',
 `dob` date DEFAULT '0000-00-00' COMMENT 'Date of Birth',
 `nic` varchar(10) DEFAULT NULL COMMENT 'NIC no',
 `address` text COMMENT 'Address Line 1',
 `city` varchar(255) DEFAULT NULL COMMENT 'City',
 `district` varchar(50) DEFAULT NULL COMMENT 'District ',
 `country` varchar(255) DEFAULT NULL COMMENT 'Country',
 `mobile` varchar(15) DEFAULT NULL COMMENT 'Mobile No',
 `contact_phone` varchar(15) DEFAULT NULL COMMENT 'Fixed Land Phone Number',
 `email` varchar(255) DEFAULT NULL COMMENT 'Email Address',
 `timer` int(4) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id_index` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=555706 DEFAULT CHARSET=utf8

Table users:

CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT,
 title varchar(100) NOT NULL,
 name varchar(255) NOT NULL DEFAULT '',
 last_name varchar(255) NOT NULL,
 username varchar(150) NOT NULL DEFAULT '',
 email varchar(100) NOT NULL DEFAULT '',
 password varchar(100) NOT NULL DEFAULT '',
 usertype varchar(25) NOT NULL DEFAULT '',
 block tinyint(4) NOT NULL DEFAULT '0',
 sendEmail tinyint(4) DEFAULT '0',
 registerDate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 lastvisitDate datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 activation varchar(100) NOT NULL DEFAULT '',
 params text NOT NULL,
 gender varchar(6) NOT NULL,
 date_of_birth date NOT NULL,
 nic_no varchar(10) NOT NULL,
 address varchar(255) NOT NULL,
 city varchar(100) NOT NULL,
 district varchar(100) NOT NULL,
 mobile varchar(15) NOT NULL,
 subscribe_sms tinyint(1) NOT NULL,
 contact_phone varchar(15) NOT NULL,
 newsletter_subscribe tinyint(1) NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY username (username),
 KEY usertype (usertype),
 KEY idx_name (name),
 KEY idx_block (block),
 KEY email (email)) 
 ENGINE=InnoDB AUTO_INCREMENT=34265 DEFAULT CHARSET=utf8

Answer 1

First I would rewrite the query like this:

SELECT r.*, u.username 
FROM 
`reservation` AS r 
INNER JOIN `users` AS u ON u.id = r.user_id 
WHERE 
r.bx_date = '2012-08-22' /* or use BETWEEN when you really have a range */
AND   r.status='1' 
ORDER BY r.id desc

The INNER JOIN is just a different syntax than yours, but it does the same. In my opinion this one is less error prone and more eye friendly, since you don't have to separate in the WHERE clause what's a join and what's not.

Since the bx_date column is of type DATE you don't need the function DATE() to make a date out of it again. In general the use of a function on a column prevents the use of an index (if exists on that column).

To keep things simple at the start, just start by adding indexes to columns which are frequently used for joins or used in WHERE clauses.

In your case the columns used for the JOIN are already primary keys, so they implicitly already have an index.

You can add an index like this:

ALTER TABLE reservation ADD INDEX idx_reservation_bx_date (bx_date); /*if I remember correctly :) */

For more info read here.

You can also use compound indexes, this are indexes over multiple columns.

UPDATE Thanks to ypercube's suggest in comments:

ALTER TABLE reservation ADD INDEX idx_reservation_bx_date_status (bx_date, status); /*if I remember correctly :) */

You can then check if or what index is used by putting an EXPLAIN in front of your query. And of course you have to measure, measure and measure if an index is good or not. Overindexing is also not a good idea, since INSERTS and UPDATES become much slower. To see if an index fastens your query you have to make sure it's not in cache somewhere, so just to test execute your query with SQL_NO_CACHE like this:

SELECT SQL_NO_CACHE r.*, u.username 
FROM 
`reservation` AS r 
INNER JOIN `users` AS u ON u.id = r.user_id 
WHERE 
r.bx_date = '2012-08-22' /* or use BETWEEN when you really have a range */
AND   r.status='1' 
ORDER BY r.id desc;

--

</UPDATE>

Another rule is, that it's not a good idea to index columns which have very few distinct values when the table has many many rows. Your status column in reservation only has 4 distinct values. Adding an index on this column renders the index useless. It can even get worse, since MySQL might use the index and then has to do an extra lookup to find the actual data corresponding to the index position.

A very good read about it is Use-the-index-Luke