Index Length

MySQL & MariaDB Other MySQL® Questions
1

Hello everyone,

I just found this great website and registered here to ask some questions. I need to optimize some mysql queries, because they are slow now.

Actually, my table structure is simple - there is “id” column (primary), “keyword” column (index) and some other columns. All my queries look like select * from table where keyword=‘xxx’

There are about 20.000 records in table, and I’m using index length of 30 for “keyword” column, but it’s still slow. Usually records in “keyword” column are about 50-60 characters length, so I’m not sure if index lenght of 30 characters is good, or should I increase (decrease) it for better performance?

Thanks!

2

For 20.000 rows you can index it at full length and do not bother.
It unlikely will cause any slowdown.

What do you mean by too slow by the way ?

3

Well, I have log slow queries option enabled, and I see that these queries are logged as slow. Also server with 2xdual Xeon CPUs has high load (mysql process uses much resources). There are about 100 tables (with 20.000 records each) per server.

If I run query select * from table where id=‘xxx’ it works much faster, so i guess it’s a problem of index? But I really need to use “keyword” column to select my data, not id.

Okay, I run this command now:

ALTER TABLE table DROP INDEX keyword ,
ADD INDEX table ( keyword )

so it will use full length index now, right?

4

Thanks Peter, I’m going to launch same query for all databases.

5

Holy crap, server load was reduced 3x after adding full index and changing some columns from INT to TINYINT

6

Good )