Solved

Designing the mysql table for a specific set of queries

Posted on 2016-09-19
3
70 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

733 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