Need help on creating indexes

[lxndr]

I'm something of a beginner as far as MySQL is concerned so please go easy on me.  I have a table with the following structure:

 

story_id  INT 7

date  INT  10

userc      varchar  45

comment  text

status    char 1

 

 

None of the columns are indexed and as the table now has over 20,000 rows I'm wanting to create some indexes to improve performance.  The table gets around 70 to 100 inserts a day and having gone through the various scripts that access it in one way or another I've found the following queries (I've replaced the variable names with 'value' to try and simplify things):

 

 

DELETE FROM table WHERE story_id = 'value' AND userc = 'value' AND date = 'value' LIMIT 1

SELECT * FROM table WHERE story_id = 'value' AND userc = 'value' ORDER BY date DESC

SELECT * FROM table WHERE story_id = 'value' ORDER BY date DESC

UPDATE table SET status = '' WHERE story_id = 'value' AND date = 'value' LIMIT 1

DELETE FROM table WHERE story_id = 'value' AND date = 'value' LIMIT 1

SELECT * FROM table WHERE userc = 'value'

SELECT * FROM table, table2 WHERE table.status <> 'R' AND  table2.avail ='0' and table.story_id = table2.rec_id  ORDER by date DESC

SELECT * FROM table WHERE status <> 'R'  AND userc = 'value' ORDER by date DESC

SELECT * FROM table WHERE status = 'U' ORDER BY date ASC

 

 

Basically I'm not sure whether to create more than one index and whether to create indexes on multiple columns.  If someone who has more experience with creating indexes could take a quick look at the queries above and make some suggestions I'd really appreciate it.

 

Many thanks in advance.

 

 

 

 

[ale8oneboy]

I don't claim to be an index expert. But from what I can tell the bulk of your queries involve the `story_id` field. Is this field a primary key? If so then you're already indexing this column as a primary key.  You may think about creating an index on the `userc` field. But if you're not having performance issues, I would not be concerned with creating indexes at this point. A little more information would be helpful.

 

What kind of performance issues are you hitting?

Which queries seem the slowest?

How fast  will the insertion rate increase as your application picks up more users?

Anyone else have any thoughts to my recommendations?

[lxndr]

I don't claim to be an index expert. But from what I can tell the bulk of your queries involve the `story_id` field. Is this field a primary key? If so then you're already indexing this column as a primary key.  You may think about creating an index on the `userc` field. But if you're not having performance issues, I would not be concerned with creating indexes at this point. A little more information would be helpful.

 

What kind of performance issues are you hitting?

Which queries seem the slowest?

How fast  will the insertion rate increase as your application picks up more users?

Anyone else have any thoughts to my recommendations?

 

Thanks for replying.

 

I don't have any specific performance data except that the website host has complained a couple of time about MySQL resource utilisation and has suggested we consider a number of things including the creation of additional indexes.  So basically, from the little I know, I'm assuming any of the queries I listed for this particular table will be accessing every row rather than just a a handful each time.  As it stands, none of the columns are unique and there is no primary key.

 

If I wanted to create an index on 'story_id' using phpMyAdmin I assume I can call the index name anything I want ... would I be right in thinking I'd need to choose index type of INDEX and then select the 'story_id' where it asks for 'field'?  There's also a box for "size" which I'm not sure about .. do I set that to the same size as the 'story_id' field .. i.e. 7 ?

 

 

[ttocskcaj]

It sounds like your host is just complaining for the sake of it..

You can make `story_id` a primary key by doing this query;

ALTER TABLE `table` ADD PRIMARY KEY (`story_id`);

Make sure you backup the database 1st

Apart from that, I don't see anything wrong with your queries, as long as they are all working properly and fast.

 

If the host doesn't accept that, you might want to consider asking them to change it for you. Or change to a different host.

[ale8oneboy]

It sounds like your host is just complaining for the sake of it..

You can make `story_id` a primary key by doing this query;

ALTER TABLE `table` ADD PRIMARY KEY (`story_id`);

Make sure you backup the database 1st

Apart from that, I don't see anything wrong with your queries, as long as they are all working properly and fast.

 

If the host doesn't accept that, you might want to consider asking them to change it for you. Or change to a different host.

 

I agree with ttocskcaj. You'll want to backup your database before making these changes. The query given will make the `story_id` the primary key as long as the field is completely unique through out the table. You may consider backing up your database again (separate file) to pick up your new table structure in your backup. Having the `story_id` being primary should be all the indexing you need for this table and queries.

[JonnoTheDev]

Your table should definately have a PRIMARY KEY. You should add indexes to fields that you are using to perform lookups on or any fields that join other tables.

 

i.e

Using a hypothetical table ' users'

 

users

====

userid (int 10 unsigned primary key)

name (varchar 30)

age (tinyint 3 unsigned)

 

Now, if I am going to be performing lookups such as:

SELECT userid,age FROM users WHERE name='John' ORDER BY userid ASC

I am going to be wanting to add an index to the field, 'name'. I could go one step further as it is a varchar field and add a FULLTEXT index (the field contains text values, you can add fulltext indexes to char, varchar, and text field types). You can read about this here http://devzone.zend.com/article/1304

 

If I am performing lookups on the field, 'age' I will wanting to be adding an index to that field also i.e

SELECT userid,name FROM users WHERE age >= 25 AND age <= 30 ORDER BY userid ASC

 

You can see the performance of a query, stating how the records have been found by using the EXPLAIN statement i.e

EXPLAIN SELECT userid,name FROM users WHERE age >= 25 AND age <= 30 ORDER BY userid ASC

If you ran the above before and after adding indexes you will see the difference in the result.

 

I recommend you use EXPLAIN on the SELECT queries that you are running. To understand the results and for more information on optimising your tables & queries I suggest you read the following:

 

[lxndr]

Thanks to everyone who responded.  I've been reading up on the whole indexing process and after a bit of trial and error using EXPLAIN have been able to make some big improvements in many of the tables.  There are still one or two queries that I'm unsure of in terms of whether I can optimize them, for example, can the following be optimized, right now EXPLAIN shows that it accesses all the rows in the tabke:

 

SELECT * FROM users_table WHERE UPPER(LEFT(user,1)) ='A' AND (status = '' OR status ='B' )  ORDER by user ASC, status DESC

 

user:  varchar (20)

status:  varchar (16)

 

I've tried adding indexes for user, status and user-status in combination but it hasn't made any difference.  Explain returns:

 

type:  all

possible keys:  status

key: null

key_len: null

Extra:  Using where; Using filesort

 

I assume the problem is being caused by the UPPER(LEFT(user,1)) fragment but not sure what to do about it ?

 

 

 

[awjudd]

I would add two more indices on top of the PRIMARY KEY which was previously mentioned.

 

They are on the following sets:

- (story_id, userc, date )

- (story_id, userc )

 

This should speed up your queries a bit.  However it won't be able to help the query with the UPPER and LEFT in it since those values don't have indexes on them.

 

~judda