CODEWITHSUNDEEP
mysqlindexing
Chris Muench
Chris Muench

Reputation: 18318

Improving performance with lots of rows (1,000,000 + records)

Below is my table and some queries I run that take a lot of time (10-40 seconds). What indexes should I add to make performance better without making the table's too big. Also I have been told that if I use 'abc%' for my like queries I can use an index. Is this true?

phppos_items

+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| name                  | varchar(255) | NO   |     | NULL    |                |
| category              | varchar(255) | NO   |     | NULL    |                |
| supplier_id           | int(11)      | YES  | MUL | NULL    |                |
| item_number           | varchar(255) | YES  | UNI | NULL    |                |
| description           | varchar(255) | NO   |     | NULL    |                |
| cost_price            | double(15,2) | NO   |     | NULL    |                |
| unit_price            | double(15,2) | NO   |     | NULL    |                |
| quantity              | double(15,2) | NO   |     | 0.00    |                |
| reorder_level         | double(15,2) | NO   |     | 0.00    |                |
| location              | varchar(255) | NO   |     | NULL    |                |
| item_id               | int(10)      | NO   | PRI | NULL    | auto_increment |
| allow_alt_description | tinyint(1)   | NO   |     | NULL    |                |
| is_serialized         | tinyint(1)   | NO   |     | NULL    |                |
| deleted               | int(1)       | NO   |     | 0       |                |
+-----------------------+--------------+------+-----+---------+----------------+

#checking if item exists
SELECT * FROM (`phppos_items`) WHERE `item_id` = 1

#Get all offset + limit, can take 20+ seconds, take longer as offset gets bigger
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 ORDER BY `name` asc LIMIT 16, 16

#Count all non deleted, haven't tested yet bug I would imagine it would take awhile as deleted is not indexed
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0

#Filtering, haven't tested yet, but I would guess it would take a while as there are no indexes on any of these fields
SELECT * FROM (`phppos_items`) WHERE `quantity` <= reorder_level AND `is_serialized` = 1 AND `description` = '' AND `deleted` = 0 ORDER BY `name` asc

#Get info about a particular item. This is pretty fast
SELECT * FROM (`phppos_items`) WHERE `item_id` = 1

#Get info about an item based on item_number, this seems pretty fast
SELECT * FROM (`phppos_items`) WHERE `item_number` = '1234'

#Search queries, very slow
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 AND `name` LIKE '%abc%' ORDER BY `name` asc
SELECT DISTINCT `category` FROM (`phppos_items`) WHERE `deleted` = 0 AND `category` LIKE '%abc%' ORDER BY `category` asc
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 AND `item_number` LIKE '%abc%' ORDER BY `item_number` asc
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 AND `name` LIKE '%abc%' ORDER BY `name` asc
SELECT * FROM (`phppos_items`) WHERE `deleted` = 0 AND `item_number` LIKE '%abc%' ORDER BY `item_number` asc
SELECT * FROM (`phppos_items`) WHERE (name LIKE '%abc%' or item_number LIKE '%abc%' or category LIKE '%abc%') and deleted=0 ORDER BY `name` asc LIMIT 16

#Category search, pretty fast
SELECT DISTINCT `category` FROM (`phppos_items`) WHERE `deleted` = 0 AND `category` LIKE '%abc%' ORDER BY `category` asc

#Get Categories, pretty fast
SELECT DISTINCT `category` FROM (`phppos_items`) WHERE `deleted` = 0 ORDER BY `category` asc

Upvotes: 3

Views: 486

Answers (3)

Jason
Jason

Reputation: 89102

Another good rule of thumb is never to use 

select *

in a non-trivial query. Instead, list out the columns you need.

If you're only checking for existence of a row, you can use 

select count(*)

Upvotes: 1

Wolph
Wolph

Reputation: 80031

Your search queries are not using any index at all and can't use an index with the current query.

If you do a like '%....%' than it is impossible to use an index.

Your options here are:

  1. Change your query to something like this: like '...%'
  2. Use a MyISAM table with Fulltext search
  3. Use a separate fulltext search engine (Sphinx, Solr, etc...)

As for your limit/offset problem.

Instead of using offset, try using something like name > 'previous name'. Although something like that will only work correctly if name is unique. In general you never want to use a limit/offset beyond 1000 since the database will have to walk through all of those rows.

Upvotes: 4

Evan
Evan

Reputation: 6161

General rule of thumb is to look at the WHERE clause and index the columns used there. Looking at what you have the first candidates would be adding indices to deleted and item_number. MySQL will put an index on the primary key for you. SHOW INDEX will show you index information for a table.

What you said about not having wildcards at the beginning of the LIKE parameter is true. Take a look at this question. The way an INDEX is built for a string is by looking at the string from start to finish and inserting it in the index that way. It looks like from your queries you may need to look into FULLTEXT indexes or possibly reworking the problem so you don't have to create FULLTEXT indexes.

Upvotes: 3

Related Questions