MySQL Indexing, best way?

Question

2.00/5 (1 vote)

See more:

I have table with 14 column, and search can be done in any fields separately or mix, so i wanted to index all columns is that a good idea to have 14 indexes?
and my other question is because i wanna search more than 1 column in same time in some cases is it good to have multi_column index?
i can't really predicte wich columns are most used for search because it's an analytical project

Posted 25-Aug-15 5:32am

Saeed.A

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-08-25T06:28:00

Solution 1

It depends:
Fewer indexes = faster insert / update, slower query
More indexes = slower insert / update, faster query

You have to weigh it up against the common usage.

One way to reduce search time is to have a term table (pretty much every single word used) and a link table to the rows that contains those words.

As you can imagine, the search is sped up, but inserting all those link rows takes time.

This is essentially what adding indexing does.

I hope that helps. It's a very poor analogy really, and it's probably poorly put too.

Let me know either way ^_^
Andy

Posted 25-Aug-15 6:28am

Andy Lanng

Comments

Saeed.A 26-Aug-15 7:46am

thanks actualy i know but i was wondering if i dont know something so what about mix column search do i have to have multi_column index?

Andy Lanng 26-Aug-15 7:58am

hmm - again depends on how you use it. If you have a fullname search and only a forename and surname field, then I would create a calculated field with an index, but this is where TSql and MySql diverge. I am a pro in the former but I've not really ever used that later :/

Saeed.A 26-Aug-15 12:25pm

by the way thanks a lot, can u help me to make a trigger to validate my data in any field in my table?

Andy Lanng 26-Aug-15 12:30pm

Dear god no! Not that I won't help you, but for the love of Pete don't validate data with triggers o_O

Saeed.A 26-Aug-15 20:55pm

so can u give a solution on this?
http://www.codeproject.com/Questions/1022366/Mysql-Trigger-validating-data
i just wanna keep it on mysql level not application level because it may change a lot

Andy Lanng 27-Aug-15 4:00am

Again, not best with MySql but if it was TSql and I had to have some Business Logic at the DB level then I would use stored procedures. If all of your inserts, updates and deletes are performed through stored procedures then you can validate at this level.

I would only ever use triggers to populate audit tables.

I have no idea what the MySql equivalent is, tho :/

But then again, it's your db. Don't let my practices stop you from using what you're comfortable with ^_^

Saeed.A 27-Aug-15 8:32am

thanks a lot.