Solved

Designing the mysql table for a specific set of queries

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best database setup for image uploads 6 61
MySQL  on Tomcat 8 43
AWS EC2 & RDS Instance 5 34
two ways encryption with php 3 24
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
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.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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