Query Very Slow when using table(s) that are not referenced

[dlite922]

I'm building a dynamic reporting system. The report can of course query multiple tables.

The query declares which tables it will access, the fields that it needs for display, and the dependent fieds or fields not in the report, but in the "filter" part.

The problem is when a "filter" is not used, the field will not show up in the dynamically built SQL.

For example, the user doesn't choose to see a report for a particular product, but for all products, so therefore I don't constraint by the product field, but the product table is still included in FROM and joined.

When I run the query manually at the prompt, it takes a very long time to run (over 2min for 50 records) and when I take out the tables that are not used the query takes 0.03 seconds to run.

Is there any way to change this MySQL behavior? to complete ignore the tables that have no fields related to them, but are used in joins.

I'm trying to prevent coding so as to put the table names based on the criteria used.

Sorry can't give actuall query example because data is sensitive, but I can try to write out a similar query:

 
/** SLOW **/
SELECT c.number, c.name FROM customer AS c, invoice AS i, ordreLine AS o WHERE c.id = i.customerID AND i.id = o.invoiceID; 
 
/** FAST **/
SELECT c.number, c.name FROM customer AS c, invoice AS i, ordreLine AS o WHERE c.id = i.customerID AND i.id = o.invoiceID AND (i.date < 2008-02-14 AND o.qty > 5); 
 
 

You can see, same tables, but when I use fields in those tables in the WHERE clause, it executes fast.

I hope i've made my problem clear, if not let me know.


Dan

[chaarmann]

It seems that you have an index on i.date or o.qty, but you have no index on either i.id or o.invoiceID !
Just make sure that you have an index on all c.id, i.customerID, i.id and o.invoiceID and run again, ist should be much faster.

If you only have a one-to-one or one-to zero match for all tables, then you don't need to build the slow cartesian product, a fast left-join would be enough.
That means if you have only one customer record that you want to enrich with data from the invoice table if it is there or not as what I understand from your description (that means if c.id and i.customerID are unique values in each table), then instead of writing "customer AS c, invoice AS i WHERE c.id = i.customerID " you can use "customer AS c, invoice AS i left join on (c.id = i.customerID )"

 
/** SLOW **/
SELECT c.number, c.name FROM customer AS c, invoice AS i, ordreLine AS o WHERE c.id = i.customerID AND i.id = o.invoiceID; 
 
/** FAST **/
SELECT c.number, c.name FROM customer AS c, invoice AS i, ordreLine AS o WHERE c.id = i.customerID AND i.id = o.invoiceID AND (i.date < 2008-02-14 AND o.qty > 5); 
 
 

You can see, same tables, but when I use fields in those tables in the WHERE clause, it executes fast.

Dan

[dlite922]

thanks for that clarification,

Since none of my queries are really this trivial, would a Left Join work this fast when i'm querying three to six tables?

I assume so, but I have to look at the manual on how to do multiple left joins.

Thanks again,

Dan

It seems that you have an index on i.date or o.qty, but you have no index on either i.id or o.invoiceID !
Just make sure that you have an index on all c.id, i.customerID, i.id and o.invoiceID and run again, ist should be much faster.

If you only have a one-to-one or one-to zero match for all tables, then you don't need to build the slow cartesian product, a fast left-join would be enough.
That means if you have only one customer record that you want to enrich with data from the invoice table if it is there or not as what I understand from your description (that means if c.id and i.customerID are unique values in each table), then instead of writing "customer AS c, invoice AS i WHERE c.id = i.customerID " you can use "customer AS c, invoice AS i left join on (c.id = i.customerID )"

[chaarmann]

thanks for that clarification,

Since none of my queries are really this trivial, would a Left Join work this fast when i'm querying three to six tables?

I assume so, but I have to look at the manual on how to do multiple left joins.

Thanks again,

Dan

Joining many tables is no performance problem in general if you made sure that the joined fields are all indexed. Also the order of the join is important. For example:
Table A has thousand records, table B and C one million. If you join B and C first , you might end up with ten-thousand records that match the join and would be looked up before matching with A, which results in 10 records at the end. Also finding all matches in the large index tables of B and C is very slow. This is very inefficient. If you would join A with B first, you would end up with 100 records only. The index table of A is small, so it is a fast match. Now only these 100 records needs to be matched with C afterwards which leads to 10 resulting records and is very efficient.
Sometimes the database can determine the best matching order automatically, but you can not rely on that. The best way is to write your query in a way which is already optimized.