I've never had a database with this many rows, so I can new to keeping the search time to a minimum.

I have a database table with 1.5M rows.

The primary key is an auto-increment id.

Then there is a composite key comprised of 4 of the columns, each row must be unique in that the 4 columns can not be exactly the same for 2 rows.

When inserting, there is an ON DUPLICATE KEY UPDATE to ensure this.

Here is a picture of the indexes on my temp server: http://i.imgur.com/IG27z5V.png, which is the same as the production server.

The php search function takes the search term and does a: (Using Laravel)

$parts = Parts::where('part_number', 'LIKE', "%$part%")->get();

There is no other index to search by when searching through the entire thing.

The search page is also SSL, which I assume is making it slower.

What recommendations do you have to speed this up?

  • 1
    A "%...%" is expensive and the part_number is in column 3 of the index. So you will scan the whole 1.5 M row index to get your answer. Can you create a specific part_number index and see how much that helps? If the prefix wildcard can be eliminated that would also allow for a much more targeted search. – RLF Jul 31 '14 at 15:44
  • 2
    Regarding "The search page is also SSL," - that (I assume you mean accessing the page via HTTPS) is likely to make very little difference. It'll stop the results being cached and the will be an initial handshake that is not present with HTTP, but it won't affect the speed of the database access at all. Your table/index scan is the issue, as per jynus' answer, not SSL. – David Spillett Jul 31 '14 at 17:05
  • There is very little you can do to speed up this query - it is not sargable! – Vérace Feb 23 '19 at 8:38

column name like '%search%' cannot take advantage of standard BTREE/B+Tree indexes on MyISAM/InnoDB, so you are effectively doing a full table scan. The index is useless, so every single row has to be compared individually.

In order to perform string searches in a performant way you have to create a FULLTEXT index and use the operator MATCH ... AGAINST.

Alternatively, you can use an external indexing engine like Sphinx or Apache Solr.

Edit: Good source material on full text search: http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql

| improve this answer | |
  • So should I add a FULLTEXT column that is just part_number since that's what I am searching by? Then do a: SELECT * FROM parts WHERE MATCH (part_number) AGAINST ('database' IN NATURAL LANGUAGE MODE); What replaces 'database'? – Anthony Jul 31 '14 at 17:12
  • @Anthony Everything you say is correct (put $part there.), in practice MySQL FULLTEXT search has its own woes: example, very common results are not shown. I recommend you to test it on a copy of the table first and explore the different modes. – jynus Jul 31 '14 at 17:23
  • I made a test, however if I put in part of a string, say im looking for 1234, it will find 1234,1234-5, it will not find 12345,001234500, like %like% does. How do I have it search parts of strings? – Anthony Jul 31 '14 at 17:36
  • I put an asterisk on each side of the search term and it worked, is there any downfalls to that? – Anthony Jul 31 '14 at 17:40
  • @Anthony Check for speed (is one of the biggest problems) and accuracy of search (in natural search results with over 50% of matches are ignored and so are search words -by default- smaller than 4 characters). – jynus Jul 31 '14 at 17:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.