Where to index correctly in MySQL tables?

I'm having a few queri开发者_运维百科es similar to this:

SELECT * 
FROM users 
WHERE cat = X 
AND cat2 = X 
AND title LIKE %X% 
AND content LIKE %X% 
AND email LIKE %X% 
AND box LIKE %X% 
AND address LIKE %X%

Those long and slightly malformed queries are done by a proprietary software, so I can't optimize them. Any ideas where I should index and improve to get better performance?

MySQL Indexes

The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

So you are out of luck for the LIKE %X% parts. For those, no index can be used and MySQL has to do a full scan of the table.

Indexing is only useful if the expression tells you something about the beginning of a value. So, the LIKE operator only is useful when the expression does not start with a wildcard.

field1 =    'value'   # Index this
field2 LIKE 'value%'  # Index this
field3 LIKE '%value%' # Do not index, beginning characters unknown.
field4 LIKE '%value%' # Do not index, beginning characters unknown.

Also for the <, >, <= and >= operators, indexing the fields can be useful.

In your situation, put an index on cat and cat2.

You won't get anywhere with the LIKE parts of that query. But you can make sure that cat and cat2 are quick, by providing a combined index for those 2 columns.

Your best bet is to have a multi-column index on cat and cat2:

INDEX( cat, cat2 )

index on your string columns will not help at all as cularis mentioned.

If many of the consecutive queries are exactly the same (I do not know if this is the case), you would greatly benefit from MySQL Query Cache.