What is running optimize on a table doing to make such a huge difference?

Question

Simple situation, two column table [ID, TEXT]. The Text column has 1-10 word phrases. 300,000 rows.

Running the query:

SELECT * FROM row 
 WHERE text LIKE '%word%' 

...took 0.1 seconds. Ok.
So I created a 2nd column, the table now has: [ID, TEXT2, TEXT2] I made TEXT2 = TEXT (using an UPDATE table SET TEXT2 = TEXT]

Then I run the query for '%word%' again, and it takes 2.4 seconds.

---

This leaves me very very stumped but after quite a lot of blind alleys, I run OPTIMIZE on the table, and it goes to about 0.2 seconds.

Two questions:

1. Does anyone know how the data structure get's itself in such a mess whereby doubling the data increases the search time for this query by a factor of 24?
2. Is it standard for an un-indexed search like this to increase at the rate of the underlying table data structure as opposed to the data in the actual column being searched?

Thanks!

Answer 1

Sounds to me like you are the victim of Query caching. The second time your run the query (after the optimize), it already has the answer cached, and therefore the result is returned instantly. Have you tried searching for different search terms. Try running the query with caching turned off as so:

SELECT SQL_NO_CACHE * FROM row WHERE text LIKE '%word%'

To see if this changes the results, or try searching for different words, but with similar number of results to ensure that your server isn't just returned a cached value.

Answer 2

The first time it does a table scan which sounds about right for the timing - no index involved.

Then you added the index and the mysql optimizer doesn't notice you've got a wildcard on the front, so it scans the entire index to find the records, then needs two more reads (one to the PK, then one into the table from there) to get the data record on top of that.

OPTIMIZE probably just updates the optimizer statistics so it knows it should scan the table again.

Answer 3

I would think that the difference is caused by the increased row length causing the table to be fragmented on the disk. Optimize will sort that problem out, leading to the search time returning to normal (give or take a bit).