Solved

Designing the mysql table for a specific set of queries

Posted on 2016-09-19
3
54 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 25

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 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now