How MySQL memory table saved the day (opens in new tab)

+1 for Solr - looking at the search on domcop.com, it seems like a perfect fit for the faceting stuff.

There might be some messiness with the regex search (looking for user-defined patterns of consonants, vowels, etc) as I've never needed to set that up, but the rest would be really clean.

See also this blog post that discusses how RoomKey uses read-only Solr instances that are repopulated daily to speed up searching for latency-insensitive data (think "does this hotel have a pool?" etc):

http://www.colinsteele.org/post/23103789647/against-the-grai...

This was initial thought as well, seems like both would fit the needs specified, multiple column search etc. I've had good experiences with both ES and Solr. They both have pretty healthy user bases and are well documented. Only hang-up is if you use primarily reg-ex style searching; turning on n-grams could work there, but it might be too slow.

Those solutions assume text is tokenizable, and suck at regex. I think they are the wrong tool for anything having to do with domain search. Furthermore, they don't play nice with continuous cron indexing.

I haven't used either elasticsearch or solr, so I don't have firsthand experience on how they compare to Sphinx search (http://sphinxsearch.com) but I really love Sphinx. Also integrates nicely with MySQL if you're already running that.

I think Solr and Sphinx pretty much tick the same boxes in terms of features and performance though, so use what you know and like.

Thanks for the info. I haven't previously come across Elastisearch or Solr. Will definitely check these out.

Does Elasticsearch or Solr work with tabular data, can you search across multiple columns?

>Elasticsearch >Register to watch

Any site that requires me to register an account before I can even look at their product is going to have a bad time.

> Unfortunately, since there are 30 different columns that can be searched on

That's your first problem. I bet the schema isn't normalized and these are all varchar columns.

~~ Normalize your schema ~~ such that the main table is a set of integers. JOIN on PK and index whatever is required. Trust me, MySQL is fast when you do it right. I have tables with 650M records and lookups are just as fast as day 1. Keep table size AS SMALL AS POSSIBLE (less columns, use ints, etc).

Use date_ids & time_ids (from standard DWH techniques) if you are storing datetimes, not datetime fields (default indexing them is 1 second resolution which is stupid).

The domains table currently has 84 fields. We collect metrics from various sources, so reducing the number of fields is not feasible. All the field types are the smallest that we could use - e.g. tinyint(4) instead of an int etc.

Since there are so many fields with data from multiple sources, we have queries running searching on individual fields. Due to this we need to have many indexes. 4GB of the 8GB is the size of the index itself.

>"That table would entirely fit in InnoDB Buffer Pool " I would like to see how it fits in the pool on 2GB linode (mentioned in the article), also, I like that people try to think before saying "big data" and grabbing some NoSQL that is not very good fit in some places or gigantic mapreduce in 67 servers.

That is partially what was done. Other than increasing the buffer size, he did a partition on the data. Together with the buffer size increase, if he created another table using the "archive" engine and kept pruning the data form the `domain` table to this archive table he would achieve the same thing with persistance.

All in all, it was a very nice solution and use case for the memory table! :)

Thanks for the link. Unfortunately, since there are 30 different columns that can be searched on, there are many indexes and the index size for the domains table itself is 4GB. I run this off a 2GB linode, so unless I add a lot more RAM, Innodb is not going to match the memory table speed.

As I understood it, the memory tables are read-only. They're like a cache. So transactions aren't needed.

"MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length."

From - http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine...

I had no idea about this either....

> MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.

You're getting downvoted because this is not Reddit. Not that we dislike memes, we just like them on Reddit, and off HN.

It confused me for a second I thought the author was on about Jon Snow of Channel 4 news...

I'm thinking that this could be a fairly easy problem and the RDBMS may be making it harder. This is probably a tricky indexing situation, so a lot of queries might be effectively unindexed. I'd like to see what the performance would be using really simple methods, like dumping it to a TSV file and doing regex searches with grep or awk. It might be surprisingly fast.