4 ms·

Sorry, I'm just not buying that it's about the file size of data. Right now I'm working on an app that works with tweets. I want to find all tweets that link t

by firasd 9y ago

Sorry, I'm just not buying that it's about the file size of data. Right now I'm working on an app that works with tweets. I want to find all tweets that link to iTunes. When I was using: > select * from `tweets` where `url` not like '%twitter.com%' and `url` like '%itunes.apple.com%' I could scale my server up to 16 CPUs, it would still take several minutes to search a few million tweets. Yesterday I added another field to the database, `is_audio_url` where I pre-compute whether the URL is an itunes link (by string matching in the app code) when I insert the record into the database. So I can do: > select * from `tweets` where `is_audio_url` = 1 And now it's blazing fast. It is just my most recent of many experiences that MySQL really struggles with text matching.

njs12345 9y ago
If you wanted to, you could even use a trigger to compute that field, and then backfill it - no changes to app code required!
qaq 9y ago
I am not selling anything to you :) You can scale your server to even 100 vCpu or whatever blackbox name the provider is using and still have same speed mainly because MySQL does not have parallel query :). BTW you none ever mentioned MySQL but you keep brining it up.
merb 9y ago
the problem is that you are probably not using the correct index. considering that when you use a '%' at the beginning of a string you need to use at least a GIN (or was it gist?) index with string ops as far as I remember but actually using % at the beginning is not recommended. I think using a tsquery might be more recommended for contain searches. at least on postgresql
- qaq 9y ago
  He is using MySQL not PG
deleted 9y ago
[deleted]
tscherno 9y ago
Create a fulltext index and use MATCH AGAINST instead of LIKE
Lazare 9y ago
Full text search in Postgres is fast if you have it configured correctly. > It is just my most recent of many experiences that MySQL 1) You're using MySQL not Postgres; given that this is a discussion about whether Postgres can compete with Elasticsearch, that's not super relevant. :) > select * from `tweets` where `url` not like '%twitter.com%' and `url` like '%itunes.apple.com%' 2) That's not how you query a full text index; that's going to be glacially slow. You need a FULLTEXT index and to use a MATCH...AGAINST query. Check out the docs[1]. [1]: https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html
- firasd 9y ago
  Fair enough, I wasn't aware of MATCH... AGAINST will have to check it out. But although I'm not going to keep going back and forth on this in HN comments it really has been my experience that Elasticsearch can do some very cool things that I've struggled to do with MySQL. I've already named the top three examples I have in mind (full text search, recommendations/related items, and trending topics i.e. finding terms used in text fields in the last week that weren't as frequently used in the last month.)
  - wutwutwutwut 9y ago
    But didn't you just say that you had not used full text search...you haven't struggled with FT - you haven't used it..
wolco 9y ago
You could do a number of things to speed it up. - Full text index - Extract the domain name to another column index it - Change mysql defaults - Change engine types. Maybe go in memory - Create lookup table of domains
sk5t 9y ago
You should really take the time to learn how storage and indexes work, and the difference between IO and CPU constraints on query performance. The database isn't magic... with the possible exception of elasticsearch, they will all disappoint you if you chuck data into them blindly and expect optimal performance on inefficient queries.
srrr 9y ago
You can directly define is_audio_url as a partial index in postgres. No need for app code.. postgresql.org/docs/current/static/indexes-partial.html
vlplabs 9y ago
Queries with %text% syntax do not use indexes by default. You can use trgm module to index this kind of queries. https://www.postgresql.org/docs/9.1/static/pgtrgm.html https://www.postgresql.org/docs/9.1/static/pgtrgm.html
- justinclift 9y ago
  Prob better to point to the current (9.6) version of that doc, rather than the no-longer-supported ancient v9.1 version. There are some differences. :) https://www.postgresql.org/docs/current/static/pgtrgm.html https://www.postgresql.org/docs/current/static/pgtrgm.html

by firasd 9y ago

njs12345 9y ago

If you wanted to, you could even use a trigger to compute that field, and then backfill it - no changes to app code required!

qaq 9y ago

I am not selling anything to you :) You can scale your server to even 100 vCpu or whatever blackbox name the provider is using and still have same speed mainly because MySQL does not have parallel query :). BTW you none ever mentioned MySQL but you keep brining it up.

merb 9y ago

the problem is that you are probably not using the correct index. considering that when you use a '%' at the beginning of a string you need to use at least a GIN (or was it gist?) index with string ops as far as I remember but actually using % at the beginning is not recommended. I think using a tsquery might be more recommended for contain searches. at least on postgresql

qaq 9y ago
He is using MySQL not PG

deleted 9y ago

[deleted]

tscherno 9y ago

Create a fulltext index and use MATCH AGAINST instead of LIKE

Lazare 9y ago

Full text search in Postgres is fast if you have it configured correctly. > It is just my most recent of many experiences that MySQL 1) You're using MySQL not Postgres; given that this is a discussion about whether Postgres can compete with Elasticsearch, that's not super relevant. :) > select * from `tweets` where `url` not like '%twitter.com%' and `url` like '%itunes.apple.com%' 2) That's not how you query a full text index; that's going to be glacially slow. You need a FULLTEXT index and to use a MATCH...AGAINST query. Check out the docs[1]. [1]: https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html

firasd 9y ago
Fair enough, I wasn't aware of MATCH... AGAINST will have to check it out. But although I'm not going to keep going back and forth on this in HN comments it really has been my experience that Elasticsearch can do some very cool things that I've struggled to do with MySQL. I've already named the top three examples I have in mind (full text search, recommendations/related items, and trending topics i.e. finding terms used in text fields in the last week that weren't as frequently used in the last month.)
- wutwutwutwut 9y ago
  But didn't you just say that you had not used full text search...you haven't struggled with FT - you haven't used it..

wolco 9y ago

You could do a number of things to speed it up. - Full text index - Extract the domain name to another column index it - Change mysql defaults - Change engine types. Maybe go in memory - Create lookup table of domains

sk5t 9y ago

You should really take the time to learn how storage and indexes work, and the difference between IO and CPU constraints on query performance. The database isn't magic... with the possible exception of elasticsearch, they will all disappoint you if you chuck data into them blindly and expect optimal performance on inefficient queries.

srrr 9y ago

You can directly define is_audio_url as a partial index in postgres. No need for app code.. postgresql.org/docs/current/static/indexes-partial.html

vlplabs 9y ago

Queries with %text% syntax do not use indexes by default. You can use trgm module to index this kind of queries. https://www.postgresql.org/docs/9.1/static/pgtrgm.html https://www.postgresql.org/docs/9.1/static/pgtrgm.html

justinclift 9y ago
Prob better to point to the current (9.6) version of that doc, rather than the no-longer-supported ancient v9.1 version. There are some differences. :) https://www.postgresql.org/docs/current/static/pgtrgm.html https://www.postgresql.org/docs/current/static/pgtrgm.html