MySQL Bugs: #11716: Extremely poor performance when joining tables and views

Bug #11716	Extremely poor performance when joining tables and views
Submitted:	4 Jul 2005 2:48	Modified:	19 Aug 2005 8:41
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.0.7 beta max	OS:	Linux (Linux (FC 4))
Assigned to:	Evgeny Potemkin	CPU Architecture:	Any

Description:
I have created several tables to keep track of information on sales reps and other types of people and organizations associated with our organization. Each rep has information in tables named party, person, rep, address, postal_address, telecomm address and electronic address along with code tables tables such as postal_address_type, electronic_address_type, telecomm_address_type and party_type.

I created one view to list all of the phone numbers including the address_id, party_id, telecomm_address_type_id, telecomm_address_type.name, the phone number and extension and called it phone_view_01. I created similar views for email addresses, fax numbers and postal addresses.

Each of these views runs quite quickly on its own (i.e., less than 0.05 seconds in most cases to return up to 1000 records. When I created a query that uses these views as tables in left join clauses 26 record took approximately 150 seconds to complete. I then took these views and replaced the view names with the select statement of each view and it took only about 0.05 seconds to return the same 26 records.

If you wish, I can provide you with the tables and data used for this as well as the views and queries used.

Thanks,

Reginald Oake
Computer Assistants
regoake@assistants.ca

How to repeat:
create a set of tables as described above (I will supply my table definitions and data as well as view definitions it you need)

Yes please provide for us the tables with their data, views and queries.
You can upload a zipped file at:

ftp://ftp.mysql.com/pub/mysql/upload/

Please use a file name which identifies this number report.

Thanks in advance.

I have uploaded a zip file called id_11716.zip to ftp.mysql.com/pub/mysql/upload/

This zip file contains three files
- warrantee.sql - a backup file of the database in question produced by mysql admin
- rep_query.sql - the query that produces the list of reps in approximately 0.05 seconds
- views.sql - several view creation queries that produce the required views

In order to test this
- create the database by running warrantee.sql
- create the views by running views.sql
- run the query contained in the file rep_query.sql
- run the query "select * from re_view_01"

Please note that the query in the file rep_query.sql is the same query as the view rep_view_01 except that instead of using the views phone_view_01, fax_view_01 and postal_address_view_01 I inserted these queries as subqueries into the query in rep_query.sql (I hope this makes sense).

Thank you.

Reginald Oake

Correction to previous comment...

The line that reads
- run the query "select * from re_view_01"
should actually be
- run the query "select * from rep_view_01"

Dear [name withheld],

Do you think this might be related to bug?:
http://bugs.mysql.com/bug.php?id=11945

When it's not set explicitly view is created with default algorithm. But it is not optimal in this case. Views which you use to replace subselects better to create with ALGORITHM=TEMPTABLE. For example "create or replace algorithm=temptable view phone_view_01 as select ... "

See also http://dev.mysql.com/doc/mysql/en/create-view.html

Thank you very much. This was very helpful.