Best way to create indexes for MySQL DB

I was wondering when is the best time to create indexes for a web app using MySQL DB? Do you create the tables and columns and at the same time generate indexes? Or would the best approach be to create the tables/columns with no indexes then when the web app is finished, go back over all the queries created and determine the best way to index each table?

Thanks!

1 Like

Yes because I planned my database before creating any table :wink:

1 Like

Really? People actually just dont throw darts at a wall to figure out what tables/fields to create? Perhaps I will rephrase the question and hopefully get a better answer. Sine usually you plan out your database layout prior to creating your database, what is the best way to determine your indexes are properly set with the queries you use? For example to determine that a multi column index would be better than a single index? Do you just manually run explain for all of your queries? Or is there a better way to monitor the queries and change accordingly?

1 Like

I too spend time thinking about relationship and possible use cases.
I find that every minute spent planning pays dividends.

Well, up to a point. There comes a time when one just has to go with it as it is.

True, even with the best planning it often ends up that modifications need to be done.

The need often shows itself when things start to scale poorly.

Running EXPLAIN may show that adding an index will improve efficiency.
Then it’s time to do ALTER

CREATEing a table specifying indexes should not be “throwing darts”.

I think of them in terms of
What and how often are other tables likely to be using that field?
Would having an index table (MySQL creates them automatically) be helpful for faster reference, or would it be a waste?

2 Likes

You could create them with your create table syntax:

CREATE TABLE " . DB_PREFIX . "configuration_groups (
parentgroupname VARCHAR(250) NOT NULL default ‘’,
groupname VARCHAR(250) NOT NULL default ‘’,
sort INT(5) NOT NULL default ‘0’,
type ENUM(‘global’,‘service’,‘product’) NOT NULL DEFAULT ‘global’,
PRIMARY KEY (groupname),
INDEX ( parentgroupname ),
INDEX ( type )
)

Sorry for the late reply but I’ve been really busy :slight_smile:

lol I love people with some sense of humous :smile:

[quote=“silly2988, post:3, topic:214686”]
what is the best way to determine your indexes are properly set with the queries you use?
[/quote] Any field that will be used in searches, that should be indexed.

When I mean used in searches I mean really used frequently.

Example, for a client database, it is likely that I will use the client number, or the last name to do my search. Most of the time, client number will be the primary key of the table and, by definition of primary key, it will already be indexed.

It least likely that people will search by first name, phone number (well, unless you’re Domino’s pizza or similar) or e-mail address. So I will not index those.

I can still do searches using those fields but it will take more time (sometimes a lot of more time) to get the outcome. But since they will only be used exceptionally, I will not index those.

But how do I know if I’m really guessing right? By asking the people who will use it which fields they will need to search, how they’re used to search.

If a field is a foreign key, it should be indexed too.

I always try to add just the right amount of indexes. The right amount speeds the results of the queries. Too many indexes will slow it down.

So, if even before planning the database structure, you have the right information about the project, you’ll know which fields should be indexed.

I hope I made myself clear. I’m too tired :slight_smile:

And, as @Mittineague said, you can always test the results by using EXPLAIN

2 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.