Ygthkb

multiple null checks in Java8

Arizona laws regarding ownership of ground glassware for chemistry usage

How can guns be countered by melee combat without raw-ability or exceptional explanations?

80-bit collision resistence because of 80-bit x87 registers?

Why does finding small effects in large studies indicate publication bias?

Isn't a semicolon (';') needed after a function declaration in C++?

How to scroll to next div using Javascript?

Why does the Binomial Theorem use combinations and not permutations for its coefficients?

Short story where Earth is given a racist governor who likes species of a certain color

Taking an academic pseudonym?

What happens if you declare more than $10,000 at the US border?

What dissuades people from lying about where they live in order to reduce state income taxes?

Why is the meaning of kanji 閑 "leisure"?

Can a planet be tidally unlocked?

Badly designed reimbursement form. What does that say about the company?

How to read the error when writing vector files in QGIS 3.0

TikZ-Tree with asymmetric siblings

Stream.findFirst different than Optional.of?

How to not forget my phone in the bathroom?

Is it ethical to apply for a job on someone's behalf?

Why is Shelob considered evil?

How do I add a strong "onion flavor" to the biryani (in restaurant style)?

Exploding Numbers

Why didn't Lorentz conclude that no object can go faster than light?

Searching through ~1M slow

How to duplicate related rowsIntegrity constraint violation in Percona XtraDB ClusterMysql might have too many indexesPrimary key auto increment best practicesBig difference between id valueConcurrency when simulating autoincrement with triggers on MySQLSELECT and UPDATE in a single query while updating selective fields of duplicate records onlyHow to insert values in junction table for many to many relationships (MySQL)?Table shows id (auto-increment) is 0 in all rowsDefault value of a column based on other column in MySQL

0

2

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?

mysql php

share

asked Jul 31 '14 at 15:25

AnthonyAnthony

1061

bumped to the homepage by Community♦ 4 hours ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

0

2

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?

mysql php

share

asked Jul 31 '14 at 15:25

AnthonyAnthony

1061

bumped to the homepage by Community♦ 4 hours ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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?

mysql php

share

asked Jul 31 '14 at 15:25

AnthonyAnthony

1061

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

share

asked Jul 31 '14 at 15:25

AnthonyAnthony

1061

share

asked Jul 31 '14 at 15:25

AnthonyAnthony

1061

asked Jul 31 '14 at 15:25

AnthonyAnthony

1061

asked Jul 31 '14 at 15:25

AnthonyAnthony

1061

1 Answer
1

active

oldest

votes

0

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

share|improve this answer

edited Jul 31 '14 at 17:28

answered Jul 31 '14 at 16:30

jynusjynus

11.1k11832

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f72856%2fsearching-through-1m-slow%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

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

share|improve this answer

edited Jul 31 '14 at 17:28

answered Jul 31 '14 at 16:30

jynusjynus

11.1k11832

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

0

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

share|improve this answer

edited Jul 31 '14 at 17:28

answered Jul 31 '14 at 16:30

jynusjynus

11.1k11832

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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

share|improve this answer

edited Jul 31 '14 at 17:28

answered Jul 31 '14 at 16:30

jynusjynus

11.1k11832

share|improve this answer

edited Jul 31 '14 at 17:28

answered Jul 31 '14 at 16:30

jynusjynus

11.1k11832

answered Jul 31 '14 at 16:30

jynusjynus

11.1k11832

answered Jul 31 '14 at 16:30

jynusjynus

11.1k11832