0

I have an InnoDB database with about 200K rows in the main table. I am experiencing EXTREMELY slow queries when trying to SELECT rows in this table based on VARCHAR (50) fields.

My queries take the form of:

SELECT * FROM table1 WHERE column1 = 'ABC3849DKJFL233489JKJFFK' and column2 = 'UIOJ3833838JJKFDL948DJ';

These queries are slowing my script down by many orders of magnitude and it is just not possible to continue my work until I fix this problem.

I have no other way to select the rows except these VARCHAR strings. Any thoughts on how to speed up these queries? Is there anything I can do here?

Chrysippus
  • 119
  • 1
  • 11
  • Simple. Create indices on `column1` and `column2`. – Eugen Rieck Jul 19 '13 at 20:40
  • http://stackoverflow.com/questions/2632347/sql-indexing-on-varchar – zod Jul 19 '13 at 21:04
  • 1
    http://stackoverflow.com/questions/1898453/does-index-on-varchar-make-performance-difference – zod Jul 19 '13 at 21:05
  • Thanks. If I sometimes select based on column1 alone but sometimes based on column1 AND column2, should I: 1) create two separate indices, one for each column; 2) create an index for column 1 AND a separate, combined index for column1/column2; or 3) just one index for column1/column2? – Chrysippus Jul 19 '13 at 22:23

1 Answers1

0

Adding a index of the two columns together should speed this up. Try:

ALTER TABLE `table1`
    ADD INDEX `dual_index` (`column1`, `column2`);
Alden W.
  • 1,362
  • 12
  • 19
  • Thanks for your reply. I'll give that a try, but can you tell me what I should do if sometimes I select by column 1 only and sometimes by both columns? For example, sometimes I would select like this: `SELECT * FROM table1 WHERE column1 = 'ABC3849DKJFL233489JKJFFK'` and sometimes it would be as the above example `SELECT * FROM table1 WHERE column1 = 'ABC3849DKJFL233489JKJFFK' and column2 = 'UIOJ3833838JJKFDL948DJ'` ... so should I make two separate indexes for each case? – Chrysippus Jul 19 '13 at 21:27
  • @halgonia Did you create any indexes and have any success? I'm having this exact issue. If I match on the first field by itself, it's FAST. If I match on the 2nd field by itself, it's FAST. I have separate indexes on each of those fields. If I match on BOTH fields in the same query, it's slow (like 0.02 seconds vs 5 seconds). I thought about creating another index on both fields, but haven't tried that yet. It seems crazy to need to do that for all permutations of my queries in my application, is that actually necessary? – Travitron Apr 02 '14 at 22:49