CODEWITHSUNDEEP
mysql
Awar Pulldozer
Awar Pulldozer

Reputation: 1101

Query takes more than two seconds

I have a query that takes over 2 seconds.

Here is my table schema:

CREATE TABLE `vouchers` (
  `id` int(11) NOT NULL,
  `voucher_dealer` int(11) NOT NULL,
  `voucher_number` varchar(20) NOT NULL,
  `voucher_customer_id` int(11) NOT NULL,
  `voucher_date` date NOT NULL,
  `voucher_added_date` datetime NOT NULL,
  `voucher_type` int(11) NOT NULL,
  `voucher_amount` double NOT NULL,
  `voucher_last_debt` double NOT NULL,
  `voucher_total_debt` double NOT NULL,
  `voucher_pay_method` int(11) NOT NULL,
  `voucher_note` text NOT NULL,
  `voucher_inserter` int(11) NOT NULL
)

The primary key is id and is auto incremented. The table has more than 1 million rows.

The query looks like this:

select * 
from vouchers 
where voucher_customer_id = ** 
  and voucher_date between date and date

and sometimes it looks like this:

select sum(voucher_amount) 
from vouchers 
where voucher_customer_id=** 
  and voucher_date> date limit 1

Is there a way to speed up my queries?

Upvotes: 0

Views: 184

Answers (2)

Mquinteiro
Mquinteiro

Reputation: 1080

Create another index for voucher_date

Upvotes: 1

Dave
Dave

Reputation: 29141

You'll want to use MySQLs "EXPLAIN" to determine what's going on and why.

http://dev.mysql.com/doc/refman/5.7/en/explain.html

To do so, simply add "EXPLAIN " prior to your statement like this:

EXPLAIN select * 
from vouchers 
where voucher_customer_id = ** 
  and voucher_date between date and date

It will give you information about available keys, how many rows it's needing to search...etc etc.

You can use that information to determine why it's running slow and how you can improve it's speed.

Once you've run it, you can use any of the many online resources that explain (no pun intended) how to use the "EXPLAIN" results. Here are some:

http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

How to optimise MySQL queries based on EXPLAIN plan

https://www.digitalocean.com/community/tutorials/how-to-optimize-queries-and-tables-in-mysql-and-mariadb-on-a-vps

Upvotes: 2

Related Questions