CODEWITHSUNDEEP
mysqldatabaseindexing
smith
smith

Reputation: 3282

using index with mysql table

my mysql database have a table with 3 columns ,

its strucure :

CREATE TABLE `Table` (
    `value1` VARCHAR(50) NOT NULL DEFAULT '',
    `value2` VARCHAR(50) NOT NULL DEFAULT '',
    `value3` TEXT NULL,
    `value4` VARCHAR(50) NULL DEFAULT NULL,
      `value5` VARCHAR(50) NULL DEFAULT NULL,
       PRIMARY KEY (`value1`, `value2`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

the first and the second columns are : varchar(50)

and they both combine the primary key the third column is text ,

the table contain about 1000000 records i doing my search using the first column it take minutes

to search a specific item.

how can i index this table to fast my search and what index type to use ?

Upvotes: 2

Views: 131

Answers (5)

satanas
satanas

Reputation: 706

Always is a bad idea to use such a long strings as indexes, but in case you really need to search it that way consider how are you filtering the query because MySQL can't perform like operations on indexes, so conditions like WHERE value1 LIKE "%mytext%" will never use indexes, instead try searching a shorter string so MySQL can convert that operation into a equality one. For example, use: value1 = "XXXXX" where "XXXXX" is a part of the string. To determine the best length of the comparision string analize the selectivity of your value1 field.

Consider too that multiple field indexes like (value1, value2) won't use the second field unless the first matches exactly. That it's not a bad index, is just so you know and understand how it works.

If that doesn't works, another solution could be store value1 and value2 in a new table (table2 for example) with an auto incremental id field, then add a foreign key from Table to table2 using ids (f.e. my_long_id) and finally create an index on table2 like: my_idx (value1, value2). The search will be something like:

SELECT t1.*
FROM 
  table2 as t2
INNER JOIN Table as t1 ON (t1.my_long_id = t2.id)
WHERE
  t2.value1 = "your_string"

Ensure that table2 has an index like (value1, value2) and that Table has a primary index on (my_long_id).

As final recommendation, add an 'id' field with AUTO_INCREMENT as PRIMARY KEY and (value1, values2) as a unique/regular key. This helps a lot because B-Tree stores sorted indexes, so using a string of 100 chars makes you waste I/O in this sorting. InnoDB determines the best position for that index at insert, probably it will need to move some indexes to another pages in order to get some space for the new one. With an auto incremental value this is easier and cheaper because it will never need to do such movements.

Upvotes: 1

Usman Mutawakil
Usman Mutawakil

Reputation: 5259

But why are you searching for a unique item on a non-unique column? Why can't you make queries based on your primary key? If for some reason you cannot then I would index value1, the column you are searching on.

CREATE INDEX 'index_name' ON 'table' (column_name)

Upvotes: 0

Vinay Pai
Vinay Pai

Reputation: 7758

What is the actual query you're executing? The index will only help if you're searching for a prefix (or exact) match. For example:

SELECT * FROM Table WHERE value1='Foo%'

will find anything that starts with Foo, and should use the index and be relatively quick. On the other hand:

SELECT * FROM Table WHERE value1='%Foo%'

will not use the index and you'll be forced to do a full table scan. If you need to do that, you should use a full-text index and query: http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

Upvotes: 3

Kusalananda
Kusalananda

Reputation: 15633

The only thing I can see that might possibly improve things would be to add a unique index to the first column. This obviously does not work if the first column is not actually unique, and it is questionable if it would be at all more efficient than the already existing primary key. The way I thought this might possibly help is if the unique index on the first column was smaller than the primary key (index scans would be quicker).

Also, you might be able to create an index on parts of your first column, maybe only the 5 or 10 first characters, that could be more efficient.

Also, after deleting and/or inserting lots of values, remember to run ANALYZE TABLE on the affected table, or even OPTIMIZE TABLE. That way, the stats for the MySQL query optimizer are updated.

Upvotes: 1

stivlo
stivlo

Reputation: 85536

A primary key of 50+50 characters? What does it contain? Are you should that the table is in 3rd normal form? It sounds that the key itself might contain some information, sounds like an alarm bell to me.

If you can change your primary key with something else much shorter and manageable, there are a few things you can try:

  • externalise text3 to a different table, matched by the new primary key
  • analyse your table to determine a more optimised length, rather than 50 chars with SELECT FROM xcve_info PROCEDURE ANALYSE()
  • change the size of the fields accordingly and if you can afford the extra space change VARCHAR to CHAR
  • add an index to value1, which probably shouldn't be part of the primary key

Always check the performance of the changes, to see if they were worth it or not.

Upvotes: 5

Related Questions