Improve company productivity with a Business Account.Sign Up

x
?
Solved

Designing the mysql table for a specific set of queries

Posted on 2016-09-19
3
Medium Priority
?
86 Views
Last Modified: 2016-09-27
HI,
I have the following table :
CREATE TABLE `note` (
  `id` char(36) NOT NULL,
  `body` text CHARACTER SET utf8mb4,
  `title` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `preview` text CHARACTER SET utf8mb4,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user` varchar(34) DEFAULT NULL,
  `conversation` varchar(34) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window


And i will be performing only the following queries on this table :
select * from note where (conversation="cid" and user="uid") or (conversation="uid" and user="cid" order by updated desc;
select * from note where conversation = "cid" order by updated;
select * from note where user="uid" and conversation is not null order by updated desc;
select * from note where id="id";

Open in new window

The first query can be alternatively thought of as where conversation, user in ("cid", "uid");
As here only the id column is indexed and user and conversation are not indexed. I think there must be some way of assigning index on the columns or any other way which could optimize these queries.

Please suggest the changes i could do to these queries to optimize them.

Thanks
0
Comment
Question by:Rohit Bajaj
  • 2
3 Comments
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 41804875
Hi!

Add this index to your table to make your queries run as optimal as possible.
create index note_userconv_ix on note(user asc, conversation asc, updated desc );

Open in new window


Regards,
     Tomas Helgi
0
 

Author Comment

by:Rohit Bajaj
ID: 41806102
HI,
What about the cost of setting an index. If i set an index on user and conversation. I have read some places that it causes the problem with insert queries.
Although i tested an insert query before and after adding an index the time taken by them is same.
And after adding index the select query that was taking previously 5 ms now takes 0.5 ms.

The table is having 8000 rows . Probably if the table grew in size having 3 columns of it indexed could cause the insert to take a lot of time.
Please provide your view on the above.

Thanks
0
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 41806229
Hi!

In your case the cost of setting additional index (or indexes) have no or minimal impact  (so small that it has no significant difference ) on insert/update/delete statements.
You will probably see small impact when you have very large table (ten to hundreds of millions of rows ) and you have 10 or more indexes on that table (which I doubt you will have that many indexes on this table unless you add columns to it) .

Regards,
     Tomas Helgi
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question