Optimize MYSQL Select query in large table

Question

Given the table:

CREATE TABLE `sample` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `vendorid` VARCHAR(45) NOT NULL,
    `year` INT(10) NOT NULL,
    `title` TEXT NOT NULL,
    `description` TEXT NOT NULL
    PRIMARY KEY (`id`) USING BTREE
)

Table size: over 7 million. All fields are not unique, except id.

Simple query:

SELECT * FROM sample WHERE title='milk'

Takes over 45s-60s to complete.

Tried to put unique index on title and description but got 1170 error.

How could I optimize it? Would be very grateful for suggestions.

Answer 1

If you expect there to not be a lot of rows, or you only want to return a handful anyway, then a non-unique index on title is the way to go. As this column is a TEXT datatype, you will need to constrain the length in some way, I've chosen 100.

create index sample_idx01 on sample (title (100))

An index doesn't require uniqueness by default.

Answer 2

An index on the title field may help like Andrew mentions, because yes you generally want to index on the predicates of your query. But one other problem that jumps out at me with your example query is the fact it's using SELECT *.

When you use SELECT *, you're using an anti-pattern that can affect query performance and result in sub-optimal query plans from being used. My guess (would need to see the EXPLAIN ANALYZE) is the query plan you're currently getting is a scan against the entire clustered index (entire table). And that may continue to be the case, even after adding a secondary index on title.

Instead you should either explicitly list out only the columns you actually need to SELECT for that given query and possibly add them to your secondary index key as well (after the title column in the definition). Or if this query is pretty commonly used and does need all the columns selected, you should make title part of the clustered (primary) index which will then automatically include all the fields of the table clustered on title.

But regardless, you should stop using SELECT * and always explicitly list out the columns you need (even if it's all of the columns of the table) to improve readability and maintainability of your queries too.

Answer 3

FULLTEXT(title)

WHERE MATCH(title) AGAINST ('milk' IN BOOLEAN MODE)

Read about FULLTEXT; it is very good for searching for "word(s)", but has limitations on the minimum word length (default 3), etc.

Answer 4

I think of another alternative, partition your table by the "year" field and spread the load across several DataFiles spread across different drives. That would cause the load to be split into multiple parts and should decrease execution time.

Answer 5

Table size: over 7 million.

This is not a large number of rows.

The first thing to do would be to investigate locks. So you can run the query in one terminal, and then in another terminal, do a SHOW FULL PROCESSLIST and see what you get. If many concurrent queries are waiting for a lock on the same table, that's a problem. If the table type is MyISAM, writes lock out reads, so one long query on a MyISAM table can lock out all other connections and make your webserver run out of processes, for example. In any case, if you value the data, it's a good idea to switch the table to InnoDB.

Assuming no locking issues, next thing to look at is the actual size of the table. You can use phpmyadmin or just query it. I'm having suspicions about that "description" column. If it is small, okay. But if you put 10 kB of text in there on average, your 7M rows table is going to use 70GB, which means even if the number of rows is small, it's definitely not something you want to access without thinking carefully about indexing.

You can do:

SELECT avg(length(title)), avg(length(description)), 
max(length(title)), max(length(description)) from yourtable;

to get an idea of what's in there, too.

If, as I suspect, the description column uses a huge amount of space, then that will be a problem. If you want to search on the small columns only (title, vendorid, year) then you need to keep that data in cache so it's fast. However, if each row has a big description column, that will fill your cache way faster than you'd want. That also increases the amount of IO required to fetch rows and filter them, making your queries slow.

So the strategy depends on whether you will use full-text search on the description or not.

If you do, then MYSQL full text search most likely won't be up to the task. Use something like Sphinx or Xapian instead.

If you don't search on the description, then it is likely you can use a MySQL fulltext index on the title column. However, I don't think MySQL can do a covering fulltext index and include the year column in it, which means we're back to the previous issue: the fulltext index will optimize searches on title, but if any filtering is done by year, then it will have to hit the table and clog up the cache with the descriptions of all the rows you don't want.

So if you don't search on description, there is a simple fix: put the description column in a separate table, linked with a foreign key. This makes the (id,title,year,vendorid) table much smaller, and able to fit in cache, so it will be less of a problem if the fulltext index can't offer coverage of the year/vendorid columns.