3
SELECT * FROM items WHERE caption LIKE 'name%' AND type = 'private' OR owner LIKE 'name%' type = 'private' ORDER BY uses DESC LIMIT 40;

Possible keys: items_caption,items_owner,items_type Key: items_uses (Got these by using the explain command)

It takes about 1.8 seconds to do that query and there are over million records in the table. I don't know how to make a index for this query and I've no control over the source so I can't modify the query.

user1563934
  • 33
  • 3
  • 1
    what is the missing operator between `LIKE 'name%'` and `type = 'private'` – Vatev Jul 30 '12 at 19:42
  • Oops. It's AND. SELECT * FROM items WHERE caption LIKE 'name%' AND type = 'private' OR owner LIKE 'name%' AND type = 'private' ORDER BY uses DESC LIMIT 40; – user1563934 Jul 30 '12 at 19:47

3 Answers3

2

You can do fulltext indexing on 'caption' and it will increase the query time significantly.

ALTER TABLE items ADD FULLTEXT(caption,owner);

Mysql v 5.6 and above support fulltext search in innodb. https://blogs.oracle.com/MySQL/entry/full_text_search_with_innodb

chheplo
  • 211
  • 3
  • 14
  • I'm using innodb so I think it's not possible. I forgot to mention it sorry. – user1563934 Jul 30 '12 at 19:46
  • Actually in newer version of mysql support fulltext search in innodb databse. https://blogs.oracle.com/MySQL/entry/full_text_search_with_innodb – chheplo Jul 30 '12 at 19:51
  • As I also failed to realize, OP can't modify the query so a fulltext will do nothing. – Vatev Jul 30 '12 at 20:28
1

You need to make a explain query first to see how is it working. We can't help you if we don't know how is the query being execute and the structure of your table. I.E, @chheplo says that you can add index on your text search

ALTER TABLE items ADD FULLTEXT(caption,owner);

But it will depend to your DB design, if you are using Mysql 5.6- and your engine is Innodb it wont work, you'll need engine = MyISAM, to be able to create text index, also you need to execute the explain to see if the select is also using it. However I think if you avoid the "LIKE" it will be faster, Performance of like '%Query%' vs full text search CONTAINS query

Regards

Community
  • 1
  • 1
jcho360
  • 3,724
  • 1
  • 15
  • 24
1

Since you've got at select *, you can't really do a covering index, but you could do a pair of composite indexes on (caption,type) and (owner, type).

ALTER TABLE items ADD INDEX (caption, type);
ALTER TABLE items ADD INDEX (owner, type);

This should cut down on internal point lookups.

I don't think you need fulltext search since the value you're comparing with your LIKE's do not start with a wildcard (%). Likes that compare with strings not starting with % can use traditional fast b-tree indexes. If you have a % at the start of the LIKE as in '%name' this wouldn't be the case.

Also, don't forget the conditions your searching are:

caption LIKE 'name%' AND type = 'private'

OR

owner LIKE 'name%' AND type = 'private'

So "ADD FULLTEXT(caption,owner)" won't help much if the caption clause fails and it tries to lookup by owner and type.

Ray
  • 40,256
  • 21
  • 101
  • 138
  • Thanks. It is a lot faster now (took 0.031 seconds) – user1563934 Jul 31 '12 at 07:47
  • I've noticed more problems with the query now. When there's query like `SELECT * FROM items WHERE caption LIKE '_name_%' AND type = 'private' OR owner LIKE '_name_%' and type = 'private' ORDER BY uses DESC LIMIT 40; ` It isn't using any indexes. Just because the name is between the underscores. Why is this happening and is there a way to fix this? – user1563934 Aug 03 '12 at 08:23
  • @user1563934 run `show create table ` and add the result to the post so I can see your table structure. The underscore should not affect anything unless you put a '%' before it. – Ray Aug 03 '12 at 13:19