Hi,
I am just running through some of my slow queries from servoy.
When creating an index in MYSQL if the query is sorting on the primary key, then should the index that you set be a combination of the where x=? and also the pk or just the column x?
e.g in select id from client where fk_current_user = 24 order by id asc limit 201;
should the index be CREATE INDEX on CLIENT (fk_current_user,id) or just CREATE INDEX on CLIENT (fk_current_user).
Thanks for the guidance.
David
Good question, I have the same question for Sybase & postgreSQL!
we set more than one tableFilterParam, per table, is it best practice, to set for every tableFilter an index? or combine them?
Experimentation told me that when i had multiple fields and then a sort based on another the index was far more effective when i add the order by to the index as well as the where fields.
i think it was something like
where status !=“Cancelled” and type=“Pending” and expert_id=123455 order by cons_add,id asc
Using the cons_add in the index made a big difference (32 seconds down to 1.2 seconds!!!) even though it was only returning 4 rows!!!
What I am not sure about is whether having the pk id in the index is necessary as well.
If there are any MYSQL gurus out there, I could perhaps do with a couple of day of consultancy to help optimise my solution and prop it up for another few years!!!
David
The time a query takes depends on how much is searched, not how much it finds.
A db server can use an index when the indexed fields from left-to-right are given in the condition.
For example, if you have an index on columns a,b,c a query on ‘where b = 2 and c = 3’ cannot use the index.
The other way around is possible, you don’t need to specify all fields, just the ones from left-to-right.
In this example, the query ‘where b = 2 and a = 1’ can use the index.
As a bonus, when the query sort order matches the index sort order (or is the opposite) a db engine may use this and does not sort the found results as they are already sorted by the index.
Rob