SELECT MAX vs ORDER BY LIMIT 1 question has been answered here several times, but if I add a WHERE clause, things change dramatically
Let’s say I have a MySQL table defined like this:
I have a query I’m using in my CodeIgniter model to fetch the count of listings of products between particular days. This works fine when there are less items in my table, but there are more than 100,000 entries in my table and to just get the output of 2 days it takes around 3-4 minutes. The longer the from and to days are apart, the more time it takes.
I have a very complex query, I’m already optimized a lot, But I couldn’t find a better way to write a more optimized query and reduce the query timings. Let me share details so you can understand it better.
I have the following MySQL table:
I have a query with multiple select and a single aggregated value, coming from a joined table, resulting an extensive and ugly GROUP BY (because of the one-to-many relation with the joined table).
It’s something like this:
I’m using the employees test database, available here: https://github.com/datacharmer/test_db
I’ll be the first to admit that I’m not great at SQL (and I probably shouldn’t be treating it like a rolling log file), but I was wondering if I could get some pointers for improving some slow queries…
I am using MySQL 5.6 and try to optimize next query:
I’m making one custom app that already has over 10.000.000 users.