Jump to content

Wanting to make this mysql database to be as fast as possible.


Coreinsanity

Recommended Posts

I am designing a system to assist a friend of mine in keeping track of how much his bills are costing over time, and to help him keep track of what he has and hasn't paid so he can better estimate how much money he will have spare.

 

Though, this is also mostly for practice and experience designing a system that can handle a large amount of information quickly.

The database being used is MySQL. This is an export from phpmyadmin edited to remove all but the table generation for the tables in question.

 

CREATE TABLE IF NOT EXISTS `bills` (
 `bill_id` int(11) NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `starting_balance` double NOT NULL,
 `ending_balance` double NOT NULL,
 `date` date NOT NULL,
 PRIMARY KEY (`bill_id`),
 KEY `date` (`date`),
 KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `payments` (
 `payment_id` int(11) NOT NULL AUTO_INCREMENT,
 `srv_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `cost` double NOT NULL,
 `payment_date` date NOT NULL,
 `paid` int(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`payment_id`),
 KEY `account_id` (`user_id`),
 KEY `date` (`payment_date`),
 KEY `srv_id` (`srv_id`),
 KEY `paid` (`paid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `services` (
 `srv_id` int(11) NOT NULL AUTO_INCREMENT,
 `service_name` varchar(255) NOT NULL,
 PRIMARY KEY (`srv_id`),
 UNIQUE KEY `service_name_UNIQUE` (`service_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `users` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(255) NOT NULL,
 `password` varchar(255) NOT NULL,
 PRIMARY KEY (`user_id`),
 UNIQUE KEY `password_UNIQUE` (`password`),
 UNIQUE KEY `username_UNIQUE` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

 

To explain the database a little:

 

> users - Fairly straight forward, user/pass with an auto incremented user_id

> bills - This is basically what glues the payments together. This holds the date of the bill, total initial balance, ending balance(Though thinking of removing this), the user_id that owns the bill, and an auto incremented ID for that bill.

> payments - This contains all the payments. They are grouped with bills by having the payments.payment_date matching bills.date.

So all the bills for 2011-05 will have bills.date LIKE '2011-05%' and all of the payments.payment_date LIKE '2011-05%'

 

A bill overview/summary is grabbed using 2 pieces of information:

> The user_id of the currently logged-in user

> The year provided, which is matched with a LIKE clause.

 

bills.date = payments.payment_date, so we can group payments with a specific billing month.

services.srv_id = payments.srv_id so we can get the name of a given service (used in a different query when editing a specific months bill payments)

 

I am grabbing the following data in a single query (Which I will paste below)

bills.date,

bills.starting_balance,

bills.ending_balance,

The total cost of a months bill (sum(payments.cost)),

the min of payments.paid (paid is either 0 or 1 for paid or not),

And then a sub-query that grabs the sum(payment.cost) where paid = 0, to get the unpaid amount in a single month.

 

The query now:

SELECT b.`date`, b.`starting_balance`, b.`ending_balance`, SUM(p.`cost`) as total_paid, MIN(p.`paid`) as all_paid,
      (SELECT IFNULL(SUM(p.`cost`), 0) FROM `payments` AS p WHERE p.`payment_date` = b.`date` AND p.`user_id` = b.`user_id` AND p.`paid` = 0) AS `total_unpaid`
FROM `bills` AS b
INNER JOIN `payments` AS p ON p.`payment_date` = b.`date`
WHERE b.`date` LIKE '2011%'  
AND b.`user_id` = 1
GROUP BY b.`date`;

 

Again, this works as intended - it will generate a single months bill information per row, for a general overview page. I am mostly unsure if it is good to grab all of that in one query, or if I should split it up into multiple queries? I am also wondering if I am doing indexes right in the database.

 

Hopefully I have explained everything needed, but if not I can clarify.

 

Thanks,

Thomas

 

Edit: Also, sorry if this is the wrong section - I didn't see a MySQL specific section.

Link to comment
Share on other sites

For anyone who is interested in speeding databases up:

 

I talked to a friend of mine who is a DBA. He said that what I am doing is basically "Shotgun Indexing" which is fail. Instead, you should make an index that covers the fields in your WHERE and ON clauses (ON being inside of JOINS). He also said you should avoid sub-queries like the plague.

 

I turned my query into two queries, the original (without sub-queries) and then the sub-query remade in a way that could be run by itself and still get all the information needed in one run. This made it run almost 2x as fast.

 

Lastly, he also said the default WAMP configuration sucked. So I investigated the configuration files and found that innodb_ settings were commented out, I un-commented innodb_buffer_pool_size and set it to 1000M and now it runs the same query that used to take 70 seconds in about 4 - 16 with 11million entries in the database. Before all of this it took about 3 minutes to run the query with only 3 million things in the database.

 

Anyway, hopefully some of this can help some one looking to speed up their database.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...