2

I'm designing an application that only does read queries to some database tables (it's a kind of search application); the queried data is updated at night by the system (so speed is less critical, while however very important here too), while application users only do fulltext searches within the stored data.
The retrieval performance is then a key point of the application.

Can you suggest links, Google terms or arguments and other material that I can read to properly design those parts of the database that will be involved in the users searches?
I already know the basics about indexes, and checking the queries with EXPLAIN, I'm reading up about table partitioning to see if there's anything that can help me.

The application will run on MySQL 5.6
So far its builtin FULLTEXT search is enough for our requirements, I'm looking more at hints on how to properly structure the database so that I don't add unnecessary slow downs (by leveraging the fact that no user query will update the data).

What other database technologies should I look for?

  • 1
    Take a look at the Sphinx engine for MySQL. It also comes included with MariaDB. – Vérace Sep 7 '14 at 19:20
  • @Vérace Thanks for the suggestion, but (I'm starting to think that using the word FULLTEXT in my question has been a bit misleading) I'm more looking toward options already available inside the DBMS (i.e. a way to organize the tables, special hints when writing queries, ...) than an external technology. So far the FULLTEXT search built in MySQL is enough for our requirements. – watery Sep 9 '14 at 16:09
  • Well, it's one to keep in mind if you run up against MySQL's native limits. One thing though, you say "an external technology" - Sphinx is no more external than InnoDB. – Vérace Sep 9 '14 at 16:33
3

Fulltext Search is a specialized technology that complements a traditional SQL database. There is a simple full text indexing feature built into MySQL, but you will probably find other alternatives that are more featureful and higher performance.

I did a presentation comparing them here: Full Text Search Throwdown.

The short answer: Sphinx Search.


Re your comment:

It seems your question is not about a specific solution but just asking for categories of performance optimizations.

  • Indexing. How to Design Indexes, Really is my presentation, which I've given several times. Here's a recording of me presenting a version of this talk: Tools and Techniques for Index Design.
  • Partitioning. Partitions Performance with MySQL 5.1 and 5.5 by Giuseppe Maxia. This was the one that made partitioning click for me. Partitioning is very powerful when it works, but it works for a small number of cases.
  • Caching. What's the fastest SQL query? The one you don't have to run at all, because you can fetch a copy of the data from a high-speed cache. This requires more case-by-case application coding to decide on the right data to cache, what format to store it in, and how to refresh it. Lots of work, but can speed up certain data access by orders of magnitude.
  • Denormalization. Although denormalization creates opportunities for data anomalies, sometimes it's the best choice to optimize for a specific query. Beware though, because denormalization degrades performance for all other queries against the same data.
  • Sharding. Useful when your data or query traffic grows so large that a single database instance can no longer process enough queries per second. Split your data horizontally, storing a subset on each shard instance. Add code to your application to decide how to send your queries to the appropriate shard. Sharding is used by MySQL Cluster, MySQL Fabric (which is still pretty alpha as of this writing), and Shard-Query.

Finally, the advice I often give about optimizing is that every method listed above optimizes for one query at the expense of others. For example, sharding is great if you read and write individual rows, but sharding makes things worse if you query for ranges of rows that span multiple shards.

  • Thanks for the suggestion, but (I'm starting to think that using the word FULLTEXT in my question has been a bit misleading) I'm more looking toward options already available inside the DBMS (i.e. a way to organize the tables, special hints when writing queries, ...) than an external technology. So far the FULLTEXT search built in MySQL is enough for our requirements. – watery Sep 9 '14 at 16:08
  • Now that's the kind of answer I was looking for :-) – watery Sep 11 '14 at 0:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.