MySQL Index Got Slower?

[csousley]

OK, I have a large database (about 200,000) entries with lots of small varchars and one big text field. The DB itself is over 500Mbs. Needless to say searching this DB takes some time.

In my fight to speed things up I optimized the table with all the entries, that made searching about 25% faster. On average a 34 second query to 22-25 seconds. Not bad but still to slow!

I then tried to index the three columns used in over 90% of the queries. I got this error:

mysql> alter table content ADD INDEX index_searchcri (date2, head, body);
ERROR 1170: BLOB column 'body' used in key specification without a key length

I then re-ran this statement without the body, just indexing the date2 and head fields. The search is still looking at all three areas even though only the two are indexed. The result was a much slower search than before.

The search that was taking 34 seconds, then down to 22 is now up at 84+ seconds?

I have a firm understanding of PHP, decent with MySQL, but have no experiece indexing DB's. Anyone see where I went wrong here?

Thanks - Cy

[tsinka]

Hi,

seems like you must specify how much data of the blob will be used to create the index, e.g. the first 20 bytes.

alter table content ADD INDEX index_searchcri (date2, head, body(20)); 

You might try to use EXPLAIN on your queries too look if MySQL uses any indexes at all.

Example:

EXPLAIN SELECT ....

[tsinka]

Hi,

seems like you must specify how much data of the blob will be used to create the index, e.g. the first 20 bytes.

alter table content ADD INDEX index_searchcri (date2, head, body(20)); 

Sorry,

for some reason my post has been posted twice 🙁

[jweissig]

Hi,

Even if you database is 500 MB’s a query should not be taking 20-30 second to run? Can you paste an example query and the table descriptions?

What are you using the database to store? It might be possible to make a minor re-design the database to accommodate the required performance.

Thanks,
- Justin

[csousley]

Here is my table:

mysql> describe content;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id | varchar(20) | | PRI | | |
| pub | varchar(30) | YES | | NULL | |
| cat | varchar(20) | YES | | NULL | |
| author | varchar(20) | YES | | NULL | |
| loc | varchar(10) | YES | | NULL | |
| date1 | varchar(20) | YES | | NULL | |
| date2 | varchar(20) | YES | | NULL | |
| capt1 | varchar(250) | YES | | NULL | |
| capt2 | varchar(250) | YES | | NULL | |
| byline | varchar(250) | YES | | NULL | |
| timestamp | varchar(20) | YES | | NULL | |
| ill | varchar(5) | YES | | NULL | |
| head | varchar(250) | YES | | NULL | |
| body | text | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
14 rows in set (0.00 sec)

And the page has two queries, so the total time is for the two, not just one.

select id, date2, head from content where ((head like '%worker credit card%') or (body like '%worker credit card%')) order by date2 desc limit 3

Total: 1

select id, date2, head from content where ((head like '%worker%') or (body like '%worker%')) and ((head like '%credit%') or (body like '%credit%')) and ((head like '%card%') or (body like '%card%')) and ((head not like '%worker credit card%') or (body not like '%worker credit card%')) order by date2 desc limit 3

Total: 3

DB Access Time = 55 sec.

The database is for archived news stories, there are alot of variables so I need the fexibility of the varchars instead of int fields that a couple of those might be able to change to. Any design sugestions would be happily accepted though!

I added the byte sugestion from above (date2, head, body(100)) and that helped for single word queries, but the longer strings took longer to search for than originally, the example above for one took 55 seconds to pull back.

Thanks for your help - Cy

[csousley]

Also, this is running on a Mac G4 400 with os x right now for building and testing. It will end up living on a X-Serve 2 proc 1Ghz server when completed. Plenty of RAM in both, 512 in current G4, over a gig in the x-serve.

-Cy

[tsinka]

Hi,

with using e.g. %worker% MySQL will not use any index at all (and search through all records in the table(s). If the field exactly contains worker you should rather use ='worker' instead of like '%worker%'. Same with the other fields

[csousley]

Will it use the index if my query is like:

select * from table where something like 'worker'

instead of

select * from table where something like '%worker%'

It will never = worker, so that wouldn't work, but if I could like worker without the wildcards that would be ok...?

-Cy

[tsinka]

Hi,

I think that this wouldn't solve the problem. You might have to use more tables. It might help if you describe a little deeper what you exactly want to to, e.g. examples of head and body that will match or not match. If you manage to create a database layout that makes it possible to fetch the records only by using id's it would speed up things a lot. But it's hard to find a solution without more information 🙂

[csousley]

It's an archive database for news stories. What I want to be able to do is search for the word 'apple' in the head (header of the story) or body (text of the story) and get results for matches like 'apple, apples, applesauce, applepie, snapple' and so on.

I have thought about parseing every story to make a database of words, that then map back to the stories based on an id. But I can't figure out how to do that without having an entry for every word in every story. That would be way to much.

If I could get every word accross all stories, that would be fine. Meaning if the word 'apple' was in a couple stories I only had one entry for apple that mapped to both stories instead of two entries for apple.

I'm not worried about splitting my information up and linking it all by id, I'm just not sure how to group or not group all the information I have.

It's a pretty basic idea, just search the head and body for keywords, it's just taking to much time to return as it stands now.

I hope this helps.

Thanks everyone - Cy

[jweissig]

Hi,

My suggestion would be to create a cross-reference table where you have a list of all the words that appear in your head and body columns. See the following layout.

CREATE TABLE page (
page_id int(10) unsigned NOT NULL auto_increment,
page_url varchar(200) NOT NULL default '',
PRIMARY KEY (page_id)
) TYPE=MyISAM;

CREATE TABLE word (
word_id int(10) unsigned NOT NULL auto_increment,
word_word varchar(50) NOT NULL default '',
PRIMARY KEY (word_id)
) TYPE=MyISAM;

CREATE TABLE occurrence (
occurrence_id int(10) unsigned NOT NULL auto_increment,
word_id int(10) unsigned NOT NULL default '0',
page_id int(10) unsigned NOT NULL default '0',
PRIMARY KEY (occurrence_id)
) TYPE=MyISAM;

Then when you area searching for a specific words you can say

SELECT p.page_url AS url,
COUNT(*) AS occurrences
FROM page p, word w, occurrence o
WHERE p.page_id = o.page_id AND
w.word_id = o.word_id AND
w.word_word = \"$keyword\"
GROUP BY p.page_id
ORDER BY occurrences DESC
LIMIT 5

And then you will not have to search the database using the "where something like '%blah%'" this is what is killing the database perforamcne. You sould notice a 1500% increase in speed.

This is not my idea it is fully outlined here http://www.onlamp.com/lpt/a/2753 and it also includes sample code to extract the words and insert the data into a database. From here if you use indexes this should improve speed even more.

Hope this helps,
- Justin

[jweissig]

Hi,

One more thing you might want to look into is that you should normalize your information. what I mean by this, is that you should create a table for all the authors and then have a foreign key to the author from the content table. You might already have something like this going but I would suggest using int’s as this should improve speed as the database uses a smaller database when matching keys (if I am incorrect please correct me.)

Thanks
- justin

[csousley]

That'll work great. Thanks to all - Cy