Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Very Slow Search with lots of data #2602

Open
stefan-kamsker opened this issue Feb 14, 2020 · 2 comments
Open

Very Slow Search with lots of data #2602

stefan-kamsker opened this issue Feb 14, 2020 · 2 comments
Labels

Comments

@stefan-kamsker
Copy link

@stefan-kamsker stefan-kamsker commented Feb 14, 2020

Hi,

we have yourls (v 1.7.4) running with 3.2 million entries in the url table.
We are experiencing a very slow search.

for example the following query is running around 25-30 seconds.

SELECT * FROM url WHERE 1=1 AND CONCAT_WS("",keyword,url,title,ip) LIKE ('%asdfasdfasdf%') ORDER BY timestamp desc LIMIT 0, 15; (26.32784 s)

Has anyone an idea / tipps to get this faster?
fe. better indexing or maybe a rewrite of the search logic is necessary.

Databases:
Mysql 8.0 or mariaDB 5.5.60

Thank you!

Best regards!

@stefan-kamsker stefan-kamsker changed the title Very Slow Search with with lots of data Very Slow Search with lots of data Feb 14, 2020
@LeoColomb

This comment has been minimized.

Copy link
Member

@LeoColomb LeoColomb commented Feb 18, 2020

Thanks for opening this issue, @stefan-kamsker.

with 3.2 million entries in the url table

I'm sure you know that represent *a lot of data.
My first advice would be to review entirely your MySQL configuration to tweak it as much as possible and improve data handling.

Another suggestion would come with a profiling. Is it possible to generate PHP profiling report on your side?

@LeoColomb LeoColomb added database and removed enhancement labels Feb 18, 2020
@stefan-kamsker

This comment has been minimized.

Copy link
Author

@stefan-kamsker stefan-kamsker commented Feb 19, 2020

Thanks @LeoColomb

it's clearly a mysql problem, not a php one.

found out that increasing innodb_buffer_pool_size is helping

also the text columns (url, title) seem to be slower than varchar
#2585
also changing this, helps a lot

SELECT * FROM url WHERE 1=1 AND CONCAT_WS("",keyword,url,title,ip) LIKE ('%asdfasdfasdf%') ORDER BY timestamp desc LIMIT 0, 15; (7.84154 s)

BR

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.