0

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?

cybersam
  • 63,203
  • 6
  • 53
  • 76
Awar Pulldozer
  • 1,071
  • 6
  • 23
  • 40
  • Do you have indexes on `voucher_customer_id` and `voucher_date`? Looking up data using a column is much faster if that column is indexed. – Tanner Swett Apr 27 '16 at 19:59
  • Since you are always querying the same two fields a combined index will drastically speed up things: ALTER TABLE vouchers ADD INDEX idx_customer_date (voucher_customer_id, voucher_date); Setting up two different indices on ...id and ...date would not help that much. – colburton Apr 28 '16 at 07:34
  • thank you man very very much ..now its tack less than 0.05 sec thanks again man – Awar Pulldozer Apr 28 '16 at 13:38

2 Answers2

2

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

Community
  • 1
  • 1
Dave
  • 28,833
  • 23
  • 113
  • 183
1

Create another index for voucher_date

Mquinteiro
  • 1,034
  • 1
  • 11
  • 31
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/12170650) – Chris Apr 28 '16 at 06:20
  • @chris, Sorry Chris but I think that it is an answer, and also I think that it is the right answer, in the other hand It is not a critique and I'm not requesting clarification. Perhaps it sound "rude" because it's short, but it was not my intention, English is not my mother tongue. – Mquinteiro Apr 28 '16 at 09:42