Using indexes to speed up queries

[DNCL]

Hello, i want to use indexes in mysql tables to speed up queries, can you give me the exact command to execute?


EDIT: found a command like this CREATE INDEX pagetext ON vb_post (pagetext);
but when i execute it i get the following error:
#1170 - BLOB/TEXT column 'pagetext' used in key specification without a key length
This is happening only in pagetext field in the whole table, other fields can be indexed, how do i fix this?

any help is appreciated

[kh99]

Like the error says, if you index a BLOB or TEXT column, you need to specify a prefix length, like CREATE INDEX pagetext ON vb_post (pagetext(N)) (and of course it will only use the first N chars of the field in the index).

You may be interested in reading this: http://dev.mysql.com/doc/refman/5.0/...n-indexes.html

[DNCL]

You can call me MySQL noob, i just want tp speed up queries by using indexes, can you give me a proper command to index the pagetext field for example please

EDIT: if i apply the command you gave me i get:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'N))' at line 1

[kh99]

You need to change N to a number - the number of characters that you want to be indexed. Sorry, I don't know how that number should be chosen. I'm not a MySQL expert or anything, I just thought I could help you with the syntax for that command.

Did you read the manual pages that I linked to? It's really just one or a few paragraphs for each of 4 sections that talk about indexes.

BTW, I'm not sure indexing on pagetext will help you at all. It could only have a chance of speeding things up if there was a query being done with a WHERE clause using that column (or maybe an ORDER BY or GROUP or something like that), and there probably isn't. Like I said, I'm not a database expert or anything, but I think blindly creating an index for every column might not be a good idea, since many of them probably won't be used but they all have to be maintained any time the table is changed. What you can do is, if you think a specific page is slow you can use debug mode to display all the queries being done by that page, then see if any are taking an unusually long time, then see if there's something to be done to speed it up.

[DNCL]

Thank you for your help, when i run one of the sql tuners via shell i get in red text

You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.