my mysql database possess a table with 3 posts ,
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 foremost and the 2nd posts are :
plus they both mix the main key
the 3rd column is
the table contain about 1000000 records i doing my search while using first column it take minutes
to look any particular item.
how do i index this table to fast my search and what index type to make use of ?
A principal key of 50+50 figures? Exactly what does it contain? Are you currently should the table is within 3rd normal form? It may sound the key itself might contain some good info, sounds somewhat alarm bell in my experience.
If you're able to improve your primary key with another thing much shorter and workable, you will find a couple of things you can test:
- externalise text3 to another table, matched up through the new primary key
- analyse your table to find out a far more optimized length, instead of 50 chars with
SELECT FROM xcve_info PROCEDURE ANALYSE()
- change how big the fields accordingly and when you really can afford the additional space change VARCHAR to CHAR
- add a catalog to
value1, which most likely should not participate the main key
Check the performance from the changes, to ascertain if these were worthwhile or otherwise.
What's the actual query you are performing? The index is only going to help if you are hunting for a prefix (or exact) match. For instance:
SELECT * FROM Table WHERE value1='Foo%'
will discover something that begins with Foo, and really should make use of the index and become relatively quick. However:
SELECT * FROM Table WHERE value1='%Foo%'
won't make use of the index and you will be instructed to perform a full table scan. If you want to do this, you need to use a complete-text index and query: http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html
The only real factor I can tell that may possibly improve things is always to give a unique index towards the first column. This clearly doesn't work when the first column isn't really unique, which is questionable if it might be whatsoever more effective compared to already established primary key. Generate an income thought this may possibly assistance is when the unique index around the first column was more compact compared to primary key (index scans could be faster).
Also, you may have the ability to create a catalog on areas of the first column, maybe just the five or ten first figures, that may be more effective.
Also, after removing and/or placing plenty of values, make sure to run Evaluate TABLE around the affected table, as well as OPTIMIZE TABLE. This way, the stats for that MySQL query optimizer are up-to-date.
Why looking for a distinctive item on the non-unique column? Why can't you are making queries according to most of your key? If for whatever reason you can't i quickly would index value1, the column you're looking for.
CREATE INDEX 'index_name' ON 'table' (column_title)