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?