CODEWITHSUNDEEP
mysqlperformance
CRAP-O
CRAP-O

Reputation: 31

Where to index correctly in MySQL tables?

I'm having a few queries 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?

Upvotes: 3

Views: 91

Answers (5)

snap
snap

Reputation: 2792

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.

Upvotes: 0

nobody
nobody

Reputation: 10645

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.

Upvotes: 1

Nanne
Nanne

Reputation: 64429

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.

Upvotes: 1

Pelle
Pelle

Reputation: 6588

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.

Upvotes: 2

Jacob
Jacob

Reputation: 43299

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.

Upvotes: 2

Related Questions