Optimizing MySQL Queries et. Indexes
Things I have learned from troubleshooting mySQL queries Links Index och prestanda i MySQL Join types (from explain) MySQL Profiling Commands # Run query without cache SELECT SQL_NO_CACHE * from users where name like 'Ol%'; # Profiling SET SESSION profiling = 1; SHOW PROFILES; SHOW PROFILE FOR QUERY 3; SHOW STATUS LIKE 'Last_Query_Cost'; How to index/optimize Optimize OR Example: SELECT * FROM users where customerNumber='qwe' OR birthDate='2020-01-01'; You need an index for each OR, which will create a merge index on modern innoDB. If you are missing an index for one OR you will have to do a full table scan. Basically, one condition can ruin the query. If you create a joined index on (customerNumber, birthDate) or (birthDate, customerNumber) they will have not effect. To use a joined index you have to do two solo queries and UNION them. ...