MySQL Query Help

[zippy]

Basic rules of efficient SQL:
1. Hit the right indexes
2. Avoid outer joins
3. Retrieve as much as possible in a single SQL statement
4. Don't do comparisons across different data types

These will help with the majority of SQL queries. Even if just stick to 1 and 4.

Just don't go overboard with indexes. You can have too many as well and this causes bulk inserts and updates to run like a dog.

An interesting thing about indexes though. There are times when an index is of no help. For example indexing the gender column is a waste of time. Indexes perform best when there are likely to be many distinct values. Bear that in mind. And if the table will never contain a lot of rows, then an index doesn't really make a difference. Ofc "a lot of rows" is a relative term but that's why they pay us the big bucks

 

[shauntir]

...and that's exactly as it should be

--snip


Now let's get down to why EXPLAIN shows that no optimisation is possible:

1. MAX - As I have indicated before MAX / MIN can be optimised through indexing (as long as MAX is being done against a table column and not a calculated value); In this case you are using an actual column "MAX( `financial_main`.`date` ) " so we could assume it's probably not the problem.
2. As someone else mentioned previously, your date functions: "MONTH( `financial_main`.`date` ) = MONTH( NOW( ) )" and "DAYOFMONTH( `financial_main`.`date` ) <= DAYOFMONTH( (SELECT MAX( `financial_main`.`date` ) FROM `financial_main`LIMIT 1 )" void the ability for any optimisations; why you ask? Because the comparison is being done against a calculated value, and until we process a record and make the calculation, we are unable to evaluate if there is a match or not; hence every row must be examined, and index optimisation is not possible.

So in the end; if you want to optimise your query your have to learn how to write the same query differently; basically it must be rewritten to evaluate a match against one of your table's columns as opposed to a calculated value; once this is done you can apply indexing to reduce the number of rows that the query needs to evaluate (i.e. some level of optimisation is achieved when we don't have to examine every row in the table)

This is what I was suggesting and you should try and do this now before your data gets too big for the query to run. Again, a stored proc would also help.

Basic rules of efficient SQL:
1. Hit the right indexes
2. Avoid outer joins
3. Retrieve as much as possible in a single SQL statement
4. Don't do comparisons across different data types

These will help with the majority of SQL queries. Even if just stick to 1 and 4.

Just don't go overboard with indexes. You can have too many as well and this causes bulk inserts and updates to run like a dog.

An interesting thing about indexes though. There are times when an index is of no help. For example indexing the gender column is a waste of time. Indexes perform best when there are likely to be many distinct values. Bear that in mind. And if the table will never contain a lot of rows, then an index doesn't really make a difference. Ofc "a lot of rows" is a relative term but that's why they pay us the big bucks

Yes, take note of this. However, you can create partial indexes on columns where there aren't many distinct values. For example, creating a partial index on a field like 'active' and you know you query the data where active = 1, a partial index would help performance.

Edit: Note about courses.

You can get a formal certification like your MCSA SQL Server 2012 etc. The first leg of the exam is querying data and touches a lot on optimization and efficient querying. Useful since the concepts apply to ALL relational database engines.

 

[dyanamo]

Pity MySQL does not support WITH statements. Would make this query a breeze and super fast.
Create a temporary table with the latest months results and left join the main table to it with the date range you want to get results for.
This will allow for any new companies that might have been added that there is no history for.

 

[[)roi(]]

Pity MySQL does not support WITH statements. Would make this query a breeze and super fast.
Create a temporary table with the latest months results and left join the main table to it with the date range you want to get results for.
This will allow for any new companies that might have been added that there is no history for.

You're correct MySQL doesn't support WITH (it's been a feature request since 2006); most probably not included because of the similarity to subqueries (i.e. not a blocking issue). I guess they also have to justify why someone would choose a paid DBMS over a free one (better SQL-99 support)

No arguments in that it improves readability though.