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
Solved

Designing the mysql table for a specific set of queries

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Whether to use true/false, yes/no or 0/1 11 67
How many transactions can mysql handle? 3 42
Optimize the query 5 43
SQL querys that gives me from one table into another. 2 24
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 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