Multiple indexes slowing down query

Create an index for each of the fields.

+ table_col1_index
+ table_col2_index
+ table_col3_index

It should give you more fexibility.

In the worst case you can hint to which index to use but I do NOT recommend using it since the parser is much smarter then you are, so it's a last option

SELECT select_list
FROM table_name USE INDEX(index_list)
WHERE condition;

I always thought an index like his composite index would cover the following searches:

(col1), (col1, col2), (col1, col2, col3)

Any columns in a where (or order by) should be indexed. Those are the fields you are searching on. Compound indexes are great if you will always be querying the same way. I tend to just leave them as single indexes and as @tray2 said, let the parser figure it out.

So I actually started with 3 single index columns. This gave me a query time of around about 6 second which I wanted to improve on.

If I add the compound indexes specific for this query, the time reduced to around 2 seconds.

However I also had to remove the single column indexes at the same time. If I left them on there adding the compounds made no difference.

Once I added the single column Index back on with the compound, the query time is back up to 6 seconds!

Laracon EU talk

https://youtu.be/HubezKbFL7E

How many records fullfill those criterias, 1, 100, ..., 10000, 10000000?

If you have more then 100 rows you should paginate and not load everything at once.

Also, see this video from Laracon 2018.

https://laracon.net/2018

Scroll down to Jonathan Reinink - he works through an example of improving eloquent queries with indexes.

Also, don't forget to check the impact of writes. Adding more indexes will slow down the save process.

Thanks. I'll check that video out.

@tray2 I am actually paginating also. This particular call has quite a lot of queries in it overall. So actually when I said it takes 6 seconds that is completing about 100 queries overall.

I realise that I need to optimise that separately but at the moment I am just trying to understand the indexing aspect. The fact is adding a certain set of indexes improves the performance considerably but then adding others pulls it all the way back again. I have other queries that need the other indexes and so would love to be able to use them all effectively.

@snapey thanks a lot for those video links. Really interesting.

One thing that he did point out - when you achieve a ref type of const within the explain query, that is as good as you are going to get! In my case, I can still seem to make considerable improvements past this by using the compound index.

Something that I have realise - my slow queries are actually sums (with where's) and I now realise that indexes do not necessarily help out these kinds of queries.

I have been trying out different indexes across the whole operation and found some interesting things:

My original slow query (with indexes) ran at about 500ms, which compounded across the whole operation to give the 12 seconds run time:

select -SUM(picked) as picked, `product_id` from `stock_movements` where `stock_movements`.`location_id` = '700020245' and `stock_movements`.`location_id` is not null and `stock_movements`.`company_id` = '8b11050c-612c-4922-8b34-d04d579e02a9' group by `product_id`

If I remove all table indexes (other than PK), this runs considerably faster at about 33ms and explain shows there are no possible keys. However the whole operation across all the other queries is slow at 10 seconds.

Adding on just a single index to my company_id speeds up the whole operation to about 6 seconds, but the sum query above is now 200ms. This sum query runs 15 times in the whole operation, so accounts for half of the run total run time. In this case it seems any form of single column index kills the speed on the sum query.

I can add a combination of compound primary keys and get this back down to 2 seconds again, but this also has detrimental effects on other queries.

My previous understanding was that indexes do not help sums, however I can see from the above that they CAN help sums, but it seems very hard/impossible to make this work alongside all the other requirements.

I guess the easiest fix is to store the sum result direct into the DB so the calculations are not being run every time (even though the values do change quite often).

Looking at the query I see some redundancy

select -SUM(picked) as picked, `product_id` 
from `stock_movements` 
where `stock_movements`.`location_id` = '700020245' 
and `stock_movements`.`location_id` is not null 
and `stock_movements`.`company_id` = '8b11050c-612c-4922-8b34-d04d579e02a9' 
group by `product_id`

You first check that the location_id = '700020245' then you check that it's not null. Remove the is not null criteria since it's rendered mute by the previous where clause.

Or you can try this

SELECT product_id, SUM(picked) 
FROM (SELECT * 
         FROM stock_movements
         WHERE location_id = '700020245'
         AND company_id = '8b11050c-612c-4922-8b34-d04d579e02a9')
GROUP BY product_id;

That way it will have a smaller selection of records to process. Not sure if it will go faster but it's worth testing.

Not knowing the complete picture, but you have mentioned a few times that you run multiple complex queries.

Perhaps you need to consider if there is common work across them where you could first produce a reduced dataset and then process that? For instance, if all queries use the same company_id or location_id.

The tables are multi-tennancy on the company ID column, so pretty much every query in my application will have a where company_id = ? on it. Do you mean there is a better way to subset this data outside of the final query?

OK, I thought I finally got somewhere on this but now it seems there is something else.

I added a compound index to the query (in the correct order) and it went from 200ms down to 1ms. Also, the other queries were not effected - this is all testing single queries direct in MySQL.

Running the same query on its own through Laravel and the debug bar shows a run time of about 10ms, so still good.

However, if I now run the full operation (with multiple queries) this particular query remains unchanged and runs at 200ms! I have checked the raw query and the explain details and they are identical between between the 1ms/10ms versions and the 200ms version.

Does anyone have any idea what could be going on here? What would cause running a quick number of successive queries together result in the individual query running so much slower than when it is being run on its own?

Query:

select -SUM(picked) as packed, `product_id` from `stock_movements` where `stock_movements`.`location_id` = '700020240' and `stock_movements`.`location_id` is not null and `stock_movements`.`company_id` = '8b11050c-612c-4922-8b34-d04d579e02a9' group by `product_id`

Explain:

| Metadata      |                                                        |
|---------------|--------------------------------------------------------|
| id            | 1                                                      |
| select_type   | SIMPLE                                                 |
| table         | stock_movements                                        |
| partitions    | null                                                   |
| type          | ref                                                    |
| possible_keys | company_id_location_id_picked_index                                |
| key           | company_id_location_id_picked_index                                           |
| key_len       | 1167                                                   |
| ref           | const,const                                            |
| rows          | 1                                                      |
| filtered      | 100                                                    |
| Extra         | Using index condition; Using temporary; Using filesort |

Remove this line from your query

and `stock_movements`.`location_id` is not null

This line makes sure it's never null

`stock_movements`.`location_id` = '700020240'

Dead or unecessary code is never good.

Thanks @tray2. I have tried that but it does not have any impact on the query speed. Actually this and `stock_movements`.`location_id` is not null part of the query is added in automatically when using the query builder. I am not sure there is any way to remove it other than writing the query manually?

I see.

My main question now is: why does running the query manually in MySQL produce a run time of 1ms, running the single query in Laravel completes in 10ms, but then running exactly the same query as part of an operation that has about 100 queries in total, increase the same query run time to 200ms?

Could it be a memory thing? Could the old result or old index be stored in some sort of cache (even though I am not specifying)?

When running queries MySQL caches them into memory so the second time you run the same query it's usually faster then the first time.

Running in mysql you are running it directly in the database no code between you and the execution. When running it i Laravel it can become a little slower since it has some safeguards and such that might just take a little longer to parse.

When you are running 100 queries you are using up alot of memory thus making MySQL swap the memory to disc and in that way increasing the execution time. You have some options to handle that

1. Trim your SQL to use a little memory as possible
2. Run some of the queries in parallel or in sequence
3. Increase the memory available to MySQL
4. Use more than one database with a load balancer

For this particular query, caching is will be tricky as it changes quite often and the result is key to logic decisions within the app.

Looking at laravel debug bar, I can see this set of queries (in its current state) is taking 6 seconds in total and using 7.26mb of memory (I checked this with the xdebug_peak_memory_usage() function too). It is running 65 queries in total and all of them are under 10ms apart from this one sum query which is showing as 200ms and runs about 15 times. The same query run on its own in Laravel or direct MySQL returns a result in less than 10ms.

@snapey I have accepted your answer as best for this question in relation to indexing. It was very interesting and helped out a lot with defining indexes for this. Unfortunately my problem still persists, but at least its not related (I don't think) to the table indexes now.

I have started a new thread for the subject change - link