| Bug #31112 | Aliased version of simple query very slow | ||
|---|---|---|---|
| Submitted: | 20 Sep 2007 14:41 | Modified: | 29 Oct 2007 16:54 |
| Reporter: | Adrian Walker | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S5 (Performance) |
| Version: | Ver 14.7 Distrib 4.1.13, for pc-linux-gn | OS: | Linux (pc-linux-gn) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | alias, performance, SELECT | ||
[20 Sep 2007 18:52]
Valeriy Kravchuk
Thank you for a problem report. Please, send SHOW CREATE TABLE results for tables from real query, and EXPLAIN results for a real query. Your simplified one just uses Cartesian product, hence it is slow.
[20 Sep 2007 20:39]
Adrian Walker
real query create tables and results
Attachment: work4.txt (text/plain), 24.38 KiB.
[29 Sep 2007 16:54]
Valeriy Kravchuk
Please, try to repeat with a newer version, 4.1.22 or 4.1.23. In case of the same problem, please, upload data for the tables that will demonstrate it.
[30 Oct 2007 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: FAST select distinct Brand_ID,Brand_ID from TopsDataDrug.dbo_DRUG_Brand; VERY SLOW: select distinct tt1.Brand_ID,tt2.Brand_ID from TopsDataDrug.dbo_DRUG_Brand tt1,TopsDataDrug.dbo_DRUG_Brand tt2; mysql> describe TopsDataDrug.dbo_DRUG_Brand; +-------------------------+--------------+------+-----+--------------------------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------+--------------+------+-----+--------------------------------------+-------+ | Brand_ID | varchar(10) | | PRI | CREATE DEF | | | Brand_Description | varchar(100) | | UNI | CREATE DEFAULT D_EmptyString AS '' | | | Brand_RecordState | smallint(6) | | | 0 | | | Brand_IsGeneric | tinyint(1) | | | 0 | | | Brand_Code | varchar(11) | | | CREATE DEFA | | | Ingredient_ID | varchar(10) | | MUL | CREATE DEF | | | Brand_RowGuid | blob | | | | | | Brand_LastContentUpdate | datetime | YES | | NULL | | +-------------------------+--------------+------+-----+--------------------------------------+-------+ mysql> explain select distinct tt1.Brand_ID,tt2.Brand_ID from -> TopsDataDrug.dbo_DRUG_Brand tt1,TopsDataDrug.dbo_DRUG_Brand tt2; +----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+ | 1 | SIMPLE | tt1 | index | NULL | PRIMARY | 30 | NULL | 6286 | Using index; Using temporary | | 1 | SIMPLE | tt2 | index | NULL | PRIMARY | 30 | NULL | 6286 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+------+------------------------------+ How to repeat: Presumably this slowdown will show up with synthetic data also. Suggested fix: ?