Databases
StackOverflow2013
Postsdbo
POHow to optimise below query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to optimise below query?
    primarykey
    data
    text
    <pre><code>SELECT r.*, u.username FROM `reservation` AS r JOIN `users` AS u WHERE u.id = r.user_id AND DATE(r.bx_date) &gt;= DATE('2012-08-22') AND DATE(r.bx_date) &lt;= DATE('2012-08-22') AND r.status='1' ORDER BY r.id desc </code></pre> <p>bx_date - booked date.</p> <p>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.</p> <p>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.</p> <p>Thanks</p> <p>Table reservation:</p> <pre><code>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 </code></pre> <p>Table users:</p> <pre><code>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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USfancyPants
    1. USTechie
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to optimise below query?
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POHow to optimise below query?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to optimise below query?
      1. This table or related slice is empty.
      1. VTDownMod
    1. COCan you show us the output from `SHOW CREATE TABLE reservation`, `SHOW CREATE TABLE users` and the EXPLAIN from the select you posted here? (Just execute the select, but with the word `EXPLAIN` in front of it).
      singulars
      1. POHow to optimise below query?
      1. USKonerak
    2. CO-1 because you haven't even tried optimizing it. At least include the output of `EXPLAIN` and say what stuff from https://dev.mysql.com/doc/refman/5.6/en/optimization.html you tried.
      singulars
      1. POHow to optimise below query?
      1. USEmil Vikström
    3. COWhat datatype is `bx_date`?
      singulars
      1. POHow to optimise below query?
      1. USBridge
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload