MySQL Forums - Optimizer & Parser Forum for MySQL Optimizer & Parser. http://forums.mysql.com/list.php?115 Tue, 18 Jun 2013 07:31:45 +0000 Phorum 5.2.19 http://forums.mysql.com/read.php?115,587032,587032#msg-587032 Optimize Inner Join With WHERE BETWEEN dates (7 replies) http://forums.mysql.com/read.php?115,587032,587032#msg-587032
Any suggestions are appreciated!

EXAMPLE:

SELECT a.UserID
FROM records AS a INNER JOIN records AS b
WHERE a.UserID = b.UserID
AND a.TimeClass = b.TimeClass
AND a.RecordID != b.RecordID
AND a.ClockIn <> b.ClockOut
AND a.ClockOut <> b.ClockIn
AND ( a.ClockIn BETWEEN b.ClockIn AND b.ClockOut
OR a.ClockOut BETWEEN b.ClockIn AND b.ClockOut
)
AND a.UserID IN (1,37491,74806,73680,13179,13220)
AND b.UserID IN (1,37491,74806,73680,13179,13220)


EXPLAIN:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: range
possible_keys: ix_accrualsum,idx_recordalerts,idx_payroll_elligible,idx_overlappcheck,idx_hourlyreport,idx_hourlyreport3
key: idx_overlappcheck
key_len: 4
ref: NULL
rows: 3766
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: ix_accrualsum,idx_recordalerts,idx_payroll_elligible,idx_overlappcheck,idx_hourlyreport,idx_hourlyreport3
key: idx_overlappcheck
key_len: 5
ref: _beta.a.UserID,_beta.a.TimeClass
rows: 82
Extra: Using where; Using index

INDEXES:
*************************** 1. row ***************************
Table: records
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: RecordID
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: records
Non_unique: 1
Key_name: ix_accrualsum
Seq_in_index: 1
Column_name: TimeClass
Collation: A
Cardinality: 17
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: records
Non_unique: 1
Key_name: ix_accrualsum
Seq_in_index: 2
Column_name: Approved
Collation: A
Cardinality: 1536
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: records
Non_unique: 1
Key_name: idx_xhours
Seq_in_index: 1
Column_name: Paid
Collation: A
Cardinality: 17
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: records
Non_unique: 1
Key_name: idx_xhours
Seq_in_index: 2
Column_name: TimeClass
Collation: A
Cardinality: 17
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 6. row ***************************
Table: records
Non_unique: 1
Key_name: idx_xhours
Seq_in_index: 3
Column_name: UserID
Collation: A
Cardinality: 331436
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 7. row ***************************
Table: records
Non_unique: 1
Key_name: idx_recordalerts
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 22267
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 8. row ***************************
Table: records
Non_unique: 1
Key_name: idx_recordalerts
Seq_in_index: 2
Column_name: Approved
Collation: A
Cardinality: 37143
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 9. row ***************************
Table: records
Non_unique: 1
Key_name: idx_recordalerts
Seq_in_index: 3
Column_name: Paid
Collation: A
Cardinality: 44649
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 10. row ***************************
Table: records
Non_unique: 1
Key_name: idx_projectid
Seq_in_index: 1
Column_name: ProjectID
Collation: A
Cardinality: 2086
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 11. row ***************************
Table: records
Non_unique: 1
Key_name: idx_payroll_elligible
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 47089
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 12. row ***************************
Table: records
Non_unique: 1
Key_name: idx_payroll_elligible
Seq_in_index: 2
Column_name: Paid
Collation: A
Cardinality: 62900
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 13. row ***************************
Table: records
Non_unique: 1
Key_name: idx_payroll_elligible
Seq_in_index: 3
Column_name: TimeClass
Collation: A
Cardinality: 103823
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 14. row ***************************
Table: records
Non_unique: 1
Key_name: idx_payroll_elligible
Seq_in_index: 4
Column_name: ClockIn
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 15. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 92659
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 16. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 2
Column_name: TimeClass
Collation: A
Cardinality: 105089
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 17. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 3
Column_name: RecordID
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 18. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 4
Column_name: ClockIn
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 19. row ***************************
Table: records
Non_unique: 1
Key_name: idx_overlappcheck
Seq_in_index: 5
Column_name: ClockOut
Collation: A
Cardinality: 8617349
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 20. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 269292
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 21. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 2
Column_name: AccountCodeID
Collation: A
Cardinality: 359056
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 22. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 3
Column_name: TimeClass
Collation: A
Cardinality: 430867
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 23. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 4
Column_name: Signed
Collation: A
Cardinality: 453544
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 24. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 5
Column_name: Approved
Collation: A
Cardinality: 574489
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 25. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport
Seq_in_index: 6
Column_name: Paid
Collation: A
Cardinality: 718112
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 26. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 1
Column_name: AccountCodeID
Collation: A
Cardinality: 74287
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 27. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 2
Column_name: TimeClass
Collation: A
Cardinality: 94696
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 28. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 3
Column_name: Signed
Collation: A
Cardinality: 94696
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 29. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 4
Column_name: Approved
Collation: A
Cardinality: 116450
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 30. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport_acct
Seq_in_index: 5
Column_name: Paid
Collation: A
Cardinality: 123104
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 31. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport3
Seq_in_index: 1
Column_name: UserID
Collation: A
Cardinality: 84483
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 32. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport3
Seq_in_index: 2
Column_name: TimeClass
Collation: A
Cardinality: 126725
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 33. row ***************************
Table: records
Non_unique: 1
Key_name: idx_hourlyreport3
Seq_in_index: 3
Column_name: Paid
Collation: A
Cardinality: 165718
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:]]> Anthony Galano Optimizer & Parser Wed, 05 Jun 2013 05:49:07 +0000 http://forums.mysql.com/read.php?115,585900,585900#msg-585900 Query optimization (8 replies) http://forums.mysql.com/read.php?115,585900,585900#msg-585900
SELECT post_modified_gmt
FROM wp_posts
WHERE post_status = 'publish'
AND post_type
IN (
'post', 'page', 'attachment', 'topic'
)
ORDER BY post_date DESC
LIMIT 1

there is composite index on post_type,post_status,post_date columns but mysql uses filesort which dramaticly slows down this query execution. Is there any way to speed up this query?]]> Jan Kowalski Optimizer & Parser Wed, 22 May 2013 14:08:50 +0000 http://forums.mysql.com/read.php?115,585323,585323#msg-585323 MySQL Optimizer: When is a Subquery Executed? (no replies) http://forums.mysql.com/read.php?115,585323,585323#msg-585323 http://oysteing.blogspot.com/2013/05/when-is-subquery-executed.html]]> Edwin Desouza Optimizer & Parser Thu, 02 May 2013 16:05:23 +0000 http://forums.mysql.com/read.php?115,581066,581066#msg-581066 Index optimized left join with group by (1 reply) http://forums.mysql.com/read.php?115,581066,581066#msg-581066 +----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+
| 1 | SIMPLE | app_devs | range | NULL | PRIMARY | 2 | NULL | 32135 | Using index for group-by |
+----+-------------+----------+-------+---------------+---------+---------+------+-------+--------------------------+

mysql> explain select app_id, name from app_devs left join apps on apps.id = app_id group by app_id;
+----+-------------+----------+--------+---------------+---------+---------+--------------------------------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+--------+---------------+---------+---------+--------------------------------+----------+-------------+
| 1 | SIMPLE | app_devs | index | NULL | PRIMARY | 6 | NULL | 32905483 | Using index |
| 1 | SIMPLE | apps | eq_ref | PRIMARY | PRIMARY | 2 | fluik_services.app_devs.app_id | 1 | |
+----+-------------+----------+--------+---------------+---------+---------+--------------------------------+----------+-------------+

Is there any way to perform left join to retrieve group by with left join that will use 'Using index for group-by'?
Personally i found that using subquery can many times give what you want from query optimizer, but maybe there is some cleaner/better way to prevent full table scan?

Possible subquery solution below:

mysql> explain select id, name from (select app_id as id, dev_id from app_devs group by app_id) as iq left join apps using (id);
+----+-------------+------------+--------+---------------+---------+---------+-------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+-------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 125 | |
| 1 | PRIMARY | apps | eq_ref | PRIMARY | PRIMARY | 2 | iq.id | 1 | |
| 2 | DERIVED | app_devs | range | NULL | PRIMARY | 2 | NULL | 32135 | Using index for group-by |
+----+-------------+------------+--------+---------------+---------+---------+-------+-------+--------------------------+

Simplified table structure below:

mysql> show create table app_devs;
`app_id` smallint(5) unsigned NOT NULL,
`dev_id` int(10) unsigned NOT NULL,
`created_at` int(11) NOT NULL,
`updated_at` int(11) NOT NULL,
PRIMARY KEY (`app_id`,`dev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

mysql> show create table apps;
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(63) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=178 DEFAULT CHARSET=utf8 COLLATE=utf8_bin |]]> Michal Kurgan Optimizer & Parser Tue, 12 Mar 2013 10:16:52 +0000 http://forums.mysql.com/read.php?115,578150,578150#msg-578150 MySQL 5.6: IN Subqueries Are Optimized away (no replies) http://forums.mysql.com/read.php?115,578150,578150#msg-578150 https://blog.mozilla.org/it/2013/01/29/in-subqueries-in-mysql-5-6-are-optimized-away/]]> Edwin Desouza Optimizer & Parser Tue, 29 Jan 2013 19:08:52 +0000 http://forums.mysql.com/read.php?115,578144,578144#msg-578144 Very slow query, help please (3 replies) http://forums.mysql.com/read.php?115,578144,578144#msg-578144
I have a very slow query (33.1319 seconds) and I don't know if there is a solution for this: 

select proveidors_treballadors.id_prov_treb, nom, coalesce(fac,0) from proveidors_treballadors
left join (select id_prov_treb, ROUND((sum(materials+stock)),2) as fac from albarans 
left join (select id_albara, sum(quantitat*multiplicatiu) as materials from comandes_albarans_families group by id_albara)
    T1 on (albarans.id_albara=T1.id_albara)
left join (select id_albara, sum(quantitat*multiplicatiu) as stock from albarans_families group by id_albara)
    T2 on (albarans.id_albara=T2.id_albara) 
group by id_prov_treb)
T3 on (proveidors_treballadors.id_prov_treb=T3.id_prov_treb)
where tipus_prov_treb = 'proveidor';

The query explain is



And design for data base, the tables are in yellow color:



Thank you!]]> Jaume Roca Optimizer & Parser Wed, 30 Jan 2013 15:05:37 +0000 http://forums.mysql.com/read.php?115,577795,577795#msg-577795 Select last row that is less than or equal to a date (20 replies) http://forums.mysql.com/read.php?115,577795,577795#msg-577795 whose date is less than or equal to a certain date. I would hope that
MySQL could do this and only read one row. However, looking at the
output of the "explain", I'm not sure what MySQL is doing. Is MySQL
reading more than one row, and if so, can the query be sped up? This
is with MySQL 5.5.29. Here is the table:

create table History (
HistoryEvent integer unsigned not null, # EventID
HistoryPlayer integer unsigned not null, # PlayerID
HistoryDate date not null,
HistoryReportID integer not null,
HistoryInitialMean smallint unsigned not null,
HistoryInitialStDev smallint unsigned not null,
HistoryFinalMean smallint unsigned not null,
HistoryFinalStDev smallint unsigned not null,
primary key ( HistoryEvent, HistoryPlayer ),
unique index EventReportID ( HistoryEvent, HistoryReportID ),
index EventInitialMean ( HistoryEvent, HistoryInitialMean ),
index EventFinalMean ( HistoryEvent, HistoryFinalMean ),
index PlayerDate ( HistoryPlayer, HistoryDate ) )
default character set latin1 collate latin1_german1_ci pack_keys=1 engine=MyISAM;

Here is the query and the output from "explain":

explain extended select * from History where HistoryPlayer = 59161 and
HistoryDate <= '2012-01-01' order by HistoryDate desc limit 0,1

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","range","PlayerDate","PlayerDate","7","",67,100.00,"Using where"

This one retrieves many rows, but the output from "explain" is the
same:

explain extended select * from History where HistoryPlayer = 59161 and
HistoryDate <= '2012-01-01' order by HistoryDate desc

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","range","PlayerDate","PlayerDate","7","",67,100.00,"Using where"

This one checks for a specific date, and "explain" says it will only
read one row:

explain extended select * from History where HistoryPlayer = 59161 and
HistoryDate = '2012-01-01' order by HistoryDate desc limit 0,1

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","ref","PlayerDate","PlayerDate","7","const,const",1,100.00,""

Here are the first two queries with a different value of
HistoryPlayer:

explain extended select * from History where HistoryPlayer = 59185 and
HistoryDate <= '2012-01-01' order by HistoryDate desc limit 0,1

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","range","PlayerDate","PlayerDate","7","",29,100.00,"Using where"

explain extended select * from History where HistoryPlayer = 59185 and
HistoryDate <= '2012-01-01' order by HistoryDate desc

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","range","PlayerDate","PlayerDate","7","",29,100.00,"Using where"]]> David Marcus Optimizer & Parser Sun, 03 Feb 2013 22:15:51 +0000 http://forums.mysql.com/read.php?115,577741,577741#msg-577741 New EXPLAIN Features in MySQL 5.6 – FORMAT=JSON actually adds more information! (no replies) http://forums.mysql.com/read.php?115,577741,577741#msg-577741 https://blog.mozilla.org/it/2013/01/22/new-explain-features-in-mysql-5-6-formatjson-actually-adds-more-information/]]> Edwin Desouza Optimizer & Parser Tue, 22 Jan 2013 23:58:49 +0000 http://forums.mysql.com/read.php?115,577695,577695#msg-577695 CPU load very very high !!!!, Please assist !! (1 reply) http://forums.mysql.com/read.php?115,577695,577695#msg-577695
My MySQL server hard configuration is as follows :-

CentOS 5.8
Dual Quad Core Processor
3 GB ram
MySQL Ver 14.14 Distrib 5.1.61

It's Stock Market website, Concurrent connections reach to 200 and the CPU usage reach 90% +

When i run the command "show processlist" i get "88 Rows in set" or more.

Most of the queries that we seee in the process list is "SELECT vtiger_contactdetails.firstname " statement !!!!!

The client is worried about the CPU load reaching that high. How can i optimize my server to sever the above SELECT query in the least amount of time and server maximum user request.

I am attaching along with this mail my sql configuration file for every one's reference for suggestion,

Thank you,

Since the board DOES NOT support attachment, i am pasting my configuration file here in this post.

PLEASE ACCEPT MY APOLOGIES !!!!

========================
[mysqld]
2 datadir=/var/lib/mysql
3 socket=/var/lib/mysql/mysql.sock
4 user=mysql
5
6 server-id=1
7 #replicate-same-server-id=0
8 log-bin=newcrmdb1-bin
9 #binlog_do_db=crmdb
10 binlog_do_db=newcrmdb
11 binlog-ignore-db=mysql,test
12 #replicate_do_db=crmdb
13 #replicate_do_db=newcrmdb
14 #auto-increment-increment = 3
15 #auto-increment-offset = 1
16 #log-slave-updates
17
18 skip_name_resolve
19
20 #master-host = 192.168.0.203
21 #master-user = replication
22 #master-password = rplpassword
23 #master-port = 3306
24
25 innodb_flush_log_at_trx_commit=0
26 innodb_flush_method=O_DIRECT
27 sync_binlog=0
28 slave-skip-errors = 1062
29
30 back_log = 75
31 # skip-innodb
32 max_connections = 100
33 #thread_stack= 150M
34 key_buffer = 256M
35 key_buffer_size = 256M
36 myisam_sort_buffer_size = 16M
37 join_buffer_size = 4M
38 read_buffer_size = 16M
39 sort_buffer_size = 8M
40 table_cache = 1024
41 table_definition_cache = 4800
42 thread_cache_size = 128
43 wait_timeout = 100
44 connect_timeout = 60
45 tmp_table_size = 300M
46 max_heap_table_size = 300M
47 max_allowed_packet = 64M
48 #max_connect_errors = 10000
49 read_rnd_buffer_size = 8M
50 bulk_insert_buffer_size = 8M
51 query_cache_size = 256M
52 query_cache_limit = 4M
53 query_cache_type = 1
54 query_prealloc_size = 65536
55 query_alloc_block_size = 131072
56 default-storage-engine = InnoDB
57 innodb_buffer_pool_size = 2G
58 #innodb_log_file_size = 768M #25% of pool size
59 innodb_lock_wait_timeout = 120
60 innodb_use_sys_malloc = 0
61 long_query_time = 1
62 log-slow-queries = /var/log/mysql-slow-query.log
63
64 # Default to using old password format for compatibility with mysql 3.x
65 # clients (those using the mysqlclient10 compatibility package).
66 #old_passwords=1
67
68 # Disabling symbolic-links is recommended to prevent assorted security risks;
69 # to do so, uncomment this line:
70 # symbolic-links=0
==================]]> Sayed Ali Optimizer & Parser Wed, 23 Jan 2013 05:32:25 +0000 http://forums.mysql.com/read.php?115,577246,577246#msg-577246 mysql subquery query optimization question (1 reply) http://forums.mysql.com/read.php?115,577246,577246#msg-577246
select some_alias.* from (Select * from MyTable) as some_alias where some_alias.key = 'some_key'

instead of

select * from MyTable where key = 'some_key'

The problem is that the first query takes more than 40 seconds to execute while the second query takes 15 milliseconds.

At first I thought it was some kind of bug in EF for constructing the query this way. After further research, however, it turned out that the first query is as fast as the second query when using other database engines (so far tested on Oracle and MS SQL Server) so I can't blame EF entirely. :(

My first question is why is the first query much slower than the second one in MySQL? Is it some kind of an optimizer issue?

My second question is is there something I can do from the MySQL side to make the first query as fast as the second one? The reason I ask is because I've exhausted every possibility I could think of to address the issue from the application code side but have found no solution/workaround.

Any suggestion/assistance would be greatly appreciated!]]> Yong Yim Optimizer & Parser Sun, 13 Jan 2013 17:30:51 +0000 http://forums.mysql.com/read.php?115,576920,576920#msg-576920 Create a New Datatype (1 reply) http://forums.mysql.com/read.php?115,576920,576920#msg-576920
The type I would like to create is what could be called an interval type and would look like this:

[1/1/2012:1/10/2012]

I would also like to create operators on the type, so operations like the one below could be done:

set @interval1 := [1/1/2012:1/10/2012]
set @interval2 := [1/5/2012:1/15/2012]

select @interval1 overlaps @interval2

The result set of the select above that uses the type interval with the operator overlaps would be:

[1/5/2012:1/10/2012]

along with other operators on the interval type.

I would like to expand the SQL of MySQL to support my new type and the operators I develop with it.

My question is, is this possible? I would like to stay within my area of programming expertise, that is C# in a Visual Studio environment.

If possible, how large an effort would this be? Although I am most comfortable in a Visual Studio environment using C#, what would be the best environment, if programming expertise was not an issue?

Thank you]]> Andy Gurzynski Optimizer & Parser Tue, 08 Jan 2013 05:01:14 +0000 http://forums.mysql.com/read.php?115,576862,576862#msg-576862 Can we expect an enhancement for derived table merge optimization? (7 replies) http://forums.mysql.com/read.php?115,576862,576862#msg-576862
For example:

select 'briefing_story_id '
from (select briefing_story_id, created_user_id
from briefing_story where briefing_story.story_type = 'CLIP') as inner_select
where inner_select.created_user_id = 12;

expands the derived table using only the where clause presented on derived table: briefing_story.story_type = 'CLIP'. Often times, the surrounding query adds on a much better filter that might lead to an index being the obvious path: inner_select.created_user_id = 12.

MariaDb claims to already have this optimization, and calls it "derived table merge".

Strangely, it seems as though MySql is already handling this same concept when selecting from a view (which seems conceptually like the same problem, but what do I know.) The view is not materialized- instead, any where clauses in the view are combined with the where clauses of the containing query.

My question is really twofold:

a) Am I correct that this feature does not currently exist in any MySql version? (I tested in 5.5.16 and 5.6.7 and neither merged the where clause.)
b) Is this feature anywhere in the development roadmap, and if so, what is the best guess of when (date and or version) it will be included in MySql?

As a side-note, I'm asking asking because we're using an O-R Mapping Tool (Microsoft's Entity Framework) combined with the MySql Connector. Together, they make heavy use of derived tables in their generated SQL.


Thanks greatly.]]> Michael Hogue Optimizer & Parser Tue, 15 Jan 2013 12:15:28 +0000 http://forums.mysql.com/read.php?115,576425,576425#msg-576425 MySQL Optimizer: InnoDB extended secondary keys (no replies) http://forums.mysql.com/read.php?115,576425,576425#msg-576425 http://glukhsv.blogspot.co.uk/2012/12/innodb-extended-secondary-keys.html]]> Edwin Desouza Optimizer & Parser Fri, 21 Dec 2012 18:12:26 +0000 http://forums.mysql.com/read.php?115,576261,576261#msg-576261 MySQL Optimizer: Favorite features in MySQL 5.6 (no replies) http://forums.mysql.com/read.php?115,576261,576261#msg-576261 http://jorgenloland.blogspot.com/2012/12/favorite-mysql-56-features-optimizer.html]]> Edwin Desouza Optimizer & Parser Tue, 18 Dec 2012 17:38:13 +0000 http://forums.mysql.com/read.php?115,575721,575721#msg-575721 How to best optimize MYSQL Database (2 replies) http://forums.mysql.com/read.php?115,575721,575721#msg-575721 http://www.funbench.com got hacked by the hackers, but after getting it back, some codes have remained in the database. I have tried many pluggins to get rid of these codes, but i couldn't find the best one for this purpose. Can you tell me any best way to get my site error free after hacking?]]> Muhammad Usman Optimizer & Parser Mon, 14 Jan 2013 10:03:16 +0000 http://forums.mysql.com/read.php?115,575412,575412#msg-575412 Optimizer for innodb does not reduce "data_free" (1 reply) http://forums.mysql.com/read.php?115,575412,575412#msg-575412 -- MySQL Version 9.10 Distrib 5.5.28
-- Fresh installation on SLES 11 SP 2 (64bit) rpm
-- innodb_file_per_table

Problem:
table status of innodb tables shows "data_free=4194304" for several tables of a freshly importet database on a freshly installed database-server! (which means fragmentation?!)

optimize table [tablename] wont change anything - tables are being recreated but data_free value is the same as before.


Since i've set "innodb_file_per_table", i thought data would be stored in the specific tables directory instead of the shared datafile?!

Why can have multiple tables have exactly the same value in data_free?
Why is optimize table not working in that case?

Any clarification would be much appreciated.]]> Sebastian Schött Optimizer & Parser Sat, 08 Dec 2012 19:33:22 +0000 http://forums.mysql.com/read.php?115,572908,572908#msg-572908 Complex query - need advise (3 replies) http://forums.mysql.com/read.php?115,572908,572908#msg-572908
I need some advice, aggregates across multiple joins, query is running horribly slow on a small amazon mysql RDS DB. Any ideas?

SELECT 'Name publisher' AS Publisher,
t.`Campaign Name` AS CampaignName,
c.Impressions AS ProviderImpressions,
c.Clicks AS ProviderClicks,
t.`Date` AS SearchDate,
t.Impressions AS TotalServedImpressions,
t.Clicks AS TotalClicks,
ROUND(t.GrossMediaCost, 2) AS GrossMediaCost,
ROUND(((t.Clicks / t.Impressions) * 100), 3) AS CTR,
ROUND((((c.Impressions - t.Impressions) / t.Impressions) * 100), 2) AS PercImp,
ROUND((((c.Clicks - t.Clicks) / t.Clicks) * 100), 2) AS CalcClicks,
ROUND((t.GrossMediaCost / (c.Impressions / 1000)), 2) AS ECPM,
ROUND((t.GrossMediaCost / c.Clicks), 5) AS ECPC
FROM (SELECT sum(Impressions) AS Impressions, sum(Clicks) AS Clicks
FROM Delivery
WHERE AdID IN
(SELECT distinct d.AdID
FROM MetaData m, Delivery d, Media mm
WHERE m.AdID = d.AdID and m.PlacementID = mm.`Placement ID 3PAS`
AND m.CampaignName = 'campaign name'
AND date_format(str_to_date(mm.`Date`, '%d-%m-%Y'), '%Y-%m-%d') = '2012-10-29')
) c,
(SELECT `Campaign Name`, `Date`, sum(Impressions) AS Impressions, sum(Clicks) AS Clicks, sum(`Gross Media Cost`) AS GrossMediaCost
FROM Media
WHERE`Placement ID 3PAS` IN
(SELECT distinct mm.`Placement ID 3PAS`
FROM MetaData m, Media mm
WHERE m.PlacementID = mm.`Placement ID 3PAS`
AND m.CampaignName = 'campaign name'
AND date_format(str_to_date(mm.`Date`, '%d-%m-%Y'), '%Y-%m-%d') = '2012-10-29')
) t
GROUP BY CampaignName, SearchDate
ORDER BY CampaignName, SearchDate]]> chris boer Optimizer & Parser Mon, 05 Nov 2012 17:12:19 +0000 http://forums.mysql.com/read.php?115,572040,572040#msg-572040 Follow up on "Query Execution Plan" (2 replies) http://forums.mysql.com/read.php?115,572040,572040#msg-572040
http://forums.mysql.com/read.php?115,532367,532367#msg-532367

As discussed before, I implemented a work around that is only somewhat satisfactory; using an in general slower more complex query to get rid of those rare catistrophic execution times.I decided to revisit this topic and discovered something interesting.

I chose a table that was behaving as a "slow fetch", using the original implementation. As a reminder, the query is as follows.

select * from <table> where time <= <time> order by time desc limit 1

I noticed that the choice of execution plan depended on the value of time supplied in the query. I wrote a test program to ask the server for the execution plan of the query above, but using a time ranging from the begining of data to the end. Remember that the data table rows are timestamped value samples of experimental data, with the time as primary index.

The output of the test program was the time used in the "explain", converted to human friendly for the test output, and the execution plan chosen by the server. My time step for this test was one day. The result was that the execution plan started with 'range' and continued to a point in time where it switched to 'index'; continuing with the latter until the end. I have included the result below, but replaced most of the redundant lines with the '...'.

2009-11-11 03:59:17 range
2009-11-12 03:59:17 range
2009-11-13 03:59:17 range
...
2012-01-07 03:59:17 range
2012-01-08 03:59:17 range
2012-01-09 03:59:17 range
2012-01-10 03:59:17 index
2012-01-11 03:59:17 index
2012-01-12 03:59:17 index
...
2012-10-22 04:59:17 index
2012-10-23 04:59:17 index
2012-10-24 04:59:17 index

The server's choice of plan depends on where the requested time falls within the table index. Fetching older data uses the 'range' plan and is much faster.]]> Chris Slominski Optimizer & Parser Thu, 25 Oct 2012 17:14:48 +0000 http://forums.mysql.com/read.php?115,570943,570943#msg-570943 JOIN query too slow (2 replies) http://forums.mysql.com/read.php?115,570943,570943#msg-570943
I'm trying to optimize some of my queries, as this database I'm working on is getting big.

I've a table with this structure:

mysql> SHOW CREATE TABLE Personas_experiencias;
+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Personas_experiencias | CREATE TABLE `Personas_experiencias` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`IdPersona` int(11) NOT NULL,
`Empresa` varchar(150) COLLATE latin1_spanish_ci NOT NULL,
`Puesto` varchar(4) COLLATE latin1_spanish_ci NOT NULL,
`Funcion` varchar(150) COLLATE latin1_spanish_ci DEFAULT NULL,
`Inicio` date DEFAULT NULL,
`Fin` date DEFAULT NULL,
`Actual` tinyint(4) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `IdPersona` (`IdPersona`)
) ENGINE=MyISAM AUTO_INCREMENT=5728 DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci |
+-----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.14 sec)

The thing is I need to do many JOINs on this table over itself, in order to look for several parameters in the same query. So, the basic query I'm trying to optimize is this:

SELECT `Personas`.`IdPersona` AS `id`,`Personas`.`NIF` AS `DNICIF`,`Personas`.`Nombre` AS `Nombre`,`Personas`.`Apellidos` AS `Apellidos`,`Personas`.`Email` AS `Email`,`Personas`.`Telefono` AS `OtrosDatos`
FROM `Personas`, `Personas_experiencias` AS `Personas_experiencias0`, `Personas_experiencias` AS `Personas_experiencias1`, `Personas_experiencias` AS `Personas_experiencias2`, `Personas_experiencias` AS `Personas_experiencias3`
WHERE (`Personas`.`IdPersona` = `Personas_experiencias0`.`IdPersona`) AND (`Personas_experiencias0`.`IdPersona` = `Personas_experiencias1`.`IdPersona`) AND (`Personas_experiencias1`.`IdPersona` = `Personas_experiencias2`.`IdPersona`) AND (`Personas_experiencias2`.`IdPersona` = `Personas_experiencias3`.`IdPersona`);

Well, the EXPLAIN for this SELECT is the following:

mysql> EXPLAIN SELECT `Personas`.`IdPersona` AS `id`,`Personas`.`NIF` AS `DNICIF`,`Personas`.`Nombre` AS `Nombre`,`Personas`.`Apellidos` AS `Apellidos`,`Personas`.`Email` AS `Email`,`Personas`.`Telefono` AS `OtrosDatos` FROM `Personas`, `Personas_experiencias` AS `Personas_experiencias0`, `Personas_experiencias` AS `Personas_experiencias1`, `Personas_experiencias` AS `Personas_experiencias2`, `Personas_experiencias` AS `Personas_experiencias3` WHERE (`Personas`.`IdPersona` = `Personas_experiencias0`.`IdPersona`) AND (`Personas_experiencias0`.`IdPersona` = `Personas_experiencias1`.`IdPersona`) AND (`Personas_experiencias1`.`IdPersona` = `Personas_experiencias2`.`IdPersona`) AND (`Personas_experiencias2`.`IdPersona` = `Personas_experiencias3`.`IdPersona`);
+----+-------------+------------------------+--------+---------------+-----------+---------+-----------------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+--------+---------------+-----------+---------+-----------------------------------------+------+--------------------------+
| 1 | SIMPLE | Personas_experiencias0 | index | IdPersona | IdPersona | 4 | NULL | 4980 | Using index |
| 1 | SIMPLE | Personas | eq_ref | PRIMARY | PRIMARY | 4 | qmn125.Personas_experiencias0.IdPersona | 1 | |
| 1 | SIMPLE | Personas_experiencias1 | ref | IdPersona | IdPersona | 4 | qmn125.Personas_experiencias0.IdPersona | 5 | Using index |
| 1 | SIMPLE | Personas_experiencias2 | ref | IdPersona | IdPersona | 4 | qmn125.Personas_experiencias1.IdPersona | 5 | Using where; Using index |
| 1 | SIMPLE | Personas_experiencias3 | ref | IdPersona | IdPersona | 4 | qmn125.Personas.IdPersona | 5 | Using where; Using index |
+----+-------------+------------------------+--------+---------------+-----------+---------+-----------------------------------------+------+--------------------------+
5 rows in set (0.03 sec)

4980 rows are the total number of rows at Personas_experiencias. I think the main problem is the first row of the explain command, and it takes up to 1 minute and 30 seconds to execute (I'd need to reduce it to at least to the 1/3 of time!).

Can someone advise how to improve that? I tried to define several indexes involving several columns but I can't find a way to reduce this!

Thanks so much in advance!]]> N. Kovac Optimizer & Parser Wed, 17 Oct 2012 01:48:38 +0000 http://forums.mysql.com/read.php?115,569510,569510#msg-569510 bulk update very slow (3 replies) http://forums.mysql.com/read.php?115,569510,569510#msg-569510
This one take 5 minutes
UPDATE scheduled_messages SET `batchID`='17' WHERE scheduled_time = '2012-09-12 15:00:00' AND status = 'scheduled' AND batchID IS NULL ORDER BY aggregatorID ASC,shortcodeID ASC LIMIT 1100

This one only take 30 seconds.
select * from scheduled_messages WHERE scheduled_time = '2012-09-12 15:00:00' AND status = 'scheduled' AND batchID IS NULL ORDER BY aggregatorID ASC,shortcodeID ASC LIMIT 1100

CREATE TABLE `scheduled_messages` (
`scheduled_message_id` bigint(20) NOT NULL AUTO_INCREMENT,
`batchID` int(11) DEFAULT NULL,
`subscriber_id` int(11) DEFAULT NULL,
`oneTime` tinyint(1) DEFAULT NULL,
`sendType` varchar(9) COLLATE utf8_unicode_ci DEFAULT NULL,
`message_type` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`message_text` tinytext COLLATE utf8_unicode_ci,
`scheduled_time` datetime DEFAULT NULL,
`status` tinytext COLLATE utf8_unicode_ci,
`aggregatorID` int(11) DEFAULT NULL,
`shortcodeID` int(11) DEFAULT NULL,
`retry_log_id` int(11) DEFAULT NULL,
PRIMARY KEY (`scheduled_message_id`),
KEY `idx_time` (`scheduled_time`),
KEY `idx_retry_log_id` (`retry_log_id`),
KEY `idx_subscriber_id` (`subscriber_id`),
KEY `batch` (`batchID`)
) ENGINE=InnoDB AUTO_INCREMENT=189331034 DEFAULT CHARSET=utf8 COLLATE=utf8_unico
de_ci |]]> Zhijian chen Optimizer & Parser Fri, 28 Sep 2012 02:19:51 +0000 http://forums.mysql.com/read.php?115,569274,569274#msg-569274 Why Query Cache Actived Returns Wrong Results? (3 replies) http://forums.mysql.com/read.php?115,569274,569274#msg-569274 I need to do some queries to tables with records very big. So this takes a lot of time, so I active cache, to make things faster, but sometimes, I get wrong results, because sometimes the result is 0. If I do the same query, with cache disabled, I get the right results. Do I need to configure another parameter? I configure query_cache_size=1G. My table is MyISAM...
Thanks]]> Daniel Estrada Optimizer & Parser Tue, 25 Sep 2012 05:43:36 +0000 http://forums.mysql.com/read.php?115,568789,568789#msg-568789 Join? Union? I am so lost. (1 reply) http://forums.mysql.com/read.php?115,568789,568789#msg-568789 I need to group and order both tables by the dates and SUM the Debits and Credits in each. (FYI settle is a date.)

something like this:

$query = "SELECT proj3_trades.settle, proj3_cash.date,
ROUND(( SUM(sale) - SUM(purch) ),0) as netTrades,
ROUND(( SUM(credit) - SUM(debit) ),0) as netCashFlow
FROM proj3_trades LEFT JOIN proj3_cash
ON proj3_trades.settle = proj3_cash.date
GROUP BY proj3_trades.settle
ORDER BY proj3_trades.settle ASC";

I unfortunately no SQL guru. And if there is one out there - thank you VERY MUCH.
good karma to all.
Cheers.]]> Hank Reardon Optimizer & Parser Tue, 18 Sep 2012 13:48:32 +0000 http://forums.mysql.com/read.php?115,568346,568346#msg-568346 How make indexes work with group by statement (2 replies) http://forums.mysql.com/read.php?115,568346,568346#msg-568346
Whenever I use group by statement with the fields that have an index on them, the index does not work especially when group by has more than one columns.

e.g.

explain select * from temp_table
group by dt1,dt2;

is there any solution for it?]]> Adnan Raza Optimizer & Parser Sun, 16 Sep 2012 04:34:43 +0000 http://forums.mysql.com/read.php?115,567376,567376#msg-567376 Query Optimizer Problem - OrderBy - 5.0.51 to 5.5.27 (2 replies) http://forums.mysql.com/read.php?115,567376,567376#msg-567376
Why the index is not used anymore with 5.5? When i use "force index (testIndex)" it works.


With 5.0.51

create database test;
use test;
create table test (id int primary key, a varchar(20), b varchar(30)) engine = innodb;
explain select * from test order by a;
create index testIndex on test(a);
explain select * from test order by a;

--> Results in:

+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+



+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+
| 1 | SIMPLE | test | index | NULL | testIndex | 63 | NULL | 1 | |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+




============================



With 5.5.27

create database test;
use test;
create table test (id int primary key, a varchar(20), b varchar(30)) engine = innodb;
explain select * from test order by a;
create index testIndex on test(a);
explain select * from test order by a;

--> Results in:


+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+


+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+]]> Dominik Bieringer Optimizer & Parser Fri, 07 Sep 2012 04:42:12 +0000 http://forums.mysql.com/read.php?115,566478,566478#msg-566478 optimize the query (5 replies) http://forums.mysql.com/read.php?115,566478,566478#msg-566478
Please give me the suggetion getting fast execution]]> ch srinivas Optimizer & Parser Thu, 30 Aug 2012 13:20:20 +0000 http://forums.mysql.com/read.php?115,565870,565870#msg-565870 Order By Slows down Joins (1 reply) http://forums.mysql.com/read.php?115,565870,565870#msg-565870
I have problem with this query

SELECT *
FROM files f
LEFT JOIN xfiles x ON x.hash = f.bhash
LEFT JOIN cate c ON c.id = f.cate
LEFT JOIN users u ON u.id = f.user
LEFT JOIN userslev ul ON u.id_lev = ul.id
LEFT JOIN (

SELECT DISTINCT x.hash, s.iis
FROM anno x
JOIN seb s ON s.iis = x.icc
WHERE x.le =0
AND (
x.eve =0
OR x.eve =2
)
GROUP BY `x`.`hash`
) AS tabel2 ON tabel2.hash = f.bhash
WHERE f.see + IFNULL( x.see, 0 ) + f.lee + IFNULL( x.lee, 0 ) >0
ORDER BY f.added DESC

Showing rows 0 - 29 ( 22,083 total, Query took 9.6523 sec)

as you can see , it takes 9 sec !

but without 'order by'

SELECT *
FROM files f
LEFT JOIN xfiles x ON x.hash = f.bhash
LEFT JOIN cate c ON c.id = f.cate
LEFT JOIN users u ON u.id = f.user
LEFT JOIN userslev ul ON u.id_lev = ul.id
LEFT JOIN (

SELECT DISTINCT x.hash, s.iis
FROM anno x
JOIN seb s ON s.iis = x.icc
WHERE x.le =0
AND (
x.eve =0
OR x.eve =2
)
GROUP BY `x`.`hash`
) AS tabel2 ON tabel2.hash = f.bhash
WHERE f.see + IFNULL( x.see, 0 ) + f.lee + IFNULL( x.lee, 0 ) >0


Showing rows 0 - 29 ( 22,083 total, Query took 0.0241 sec)

only 0.0241 sec :)

............

is there a way to have ' order by' with good-time?

............

indexes:


Indexes: (files)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No ihash 27815 A
Edit Drop filename BTREE No No filename 27815 A
Edit Drop cate BTREE No No cate 45 A
Edit Drop user BTREE No No user 1264 A
Edit Drop bhash BTREE No No bhash (20) 27815 A
Edit Drop added BTREE No No added 27815 A
Edit Drop filename_2 FULLTEXT No No filename 1



Indexes: (xfiles)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 8196 A
Edit Drop hash BTREE Yes No hash 8196 A
Edit Drop see BTREE No No see 37 A
Edit Drop leec BTREE No No lee25 A


Indexes: (cate)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 51 A


Indexes: (users)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 39776 A
Edit Drop username BTREE Yes No username 39776 A
Edit Drop id_lev BTREE No No id_lev 19 A


Indexes: (userslev)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop base BTREE Yes No id 22 A


Indexes: (anno)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 23636 A
Edit Drop icc BTREE No No icc 2626 A
Edit Drop eve BTREE No No eve 8 A
Edit Drop hash BTREE No No hash 5909 A
Edit Drop le BTREE No No le 875 A


Indexes: (seb)

Action Keyname Type Unique Packed Column Cardinality Collation Null Comment
Edit Drop PRIMARY BTREE Yes No id 58 A
Edit Drop iis BTREE Yes No iis 58 A YES


EXPLAIN:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY f ALL NULL NULL NULL NULL 27819 Using temporary; Using filesort
1 PRIMARY x eq_ref hash hash 20 f.bhash 1 Using where
1 PRIMARY c eq_ref PRIMARY PRIMARY 4 f.cate 1
1 PRIMARY u eq_ref PRIMARY PRIMARY 4 f.user 1
1 PRIMARY ul eq_ref base base 4 u.id_lev 1
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1176
2 DERIVED s index iis iis 5 NULL 58 Using index; Using temporary; Using filesort
2 DERIVED x ref icc,eve,le icc 4 s.iis 9 Using where]]> Sami BH Optimizer & Parser Fri, 24 Aug 2012 02:56:04 +0000 http://forums.mysql.com/read.php?115,565865,565865#msg-565865 CONCAT and Index in where clause (4 replies) http://forums.mysql.com/read.php?115,565865,565865#msg-565865
I've got a really slow query (which I can't change) using the concat function in the where clause.

The query looks like :
SELECT * from MyTABLE WHERE CONCAT(YEAR,'-',MONTH,'-',DAY) = '2012-08-21';

Where 'YEAR', 'MONTH' and 'DAY' are three fields of type CHAR.

I tried to add an index only on the column YEAR (I don't think an Index on the three columns would be usefull because of the CONCAT function).

I can't get the index to be used in this case. I've seen some cases where there are types INT and CHAR in the CONCAT function, and so a function like CAST must be used to convert the INT type, but this is not my case.

Do you have any idea on how to get this fixed ?

(I'm looking with EXPLAIN SELECT ..., but the index is not listed as "possible_keys").]]> Aurelien PANIZZA Optimizer & Parser Tue, 28 Aug 2012 08:50:20 +0000 http://forums.mysql.com/read.php?115,565839,565839#msg-565839 always uses filesort even after adding indexes (1 reply) http://forums.mysql.com/read.php?115,565839,565839#msg-565839
EXPLAIN SELECT score FROM score_history ORDER BY score;

this shows "using index" under the section "extra" in output.

But when i fire :

EXPLAIN SELECT * FROM score_history ORDER BY score;

this shows "using filesort" under the section "extra" in output.]]> Pranav Gore Optimizer & Parser Thu, 23 Aug 2012 04:52:11 +0000 http://forums.mysql.com/read.php?115,565766,565766#msg-565766 Simple query spending too much time in 'statistics' state without reason (3 replies) http://forums.mysql.com/read.php?115,565766,565766#msg-565766
I have a simple query on a single table which contains millions of rows.

SELECT a, c
FROM mytable
WHERE a IN (list of ids)
AND b IN (list of others ids)

The table 'mytable' has a primary index on (a, b). Both a and b are integer.
I use InnoDB.

CREATE TABLE `mytable` (
`a` INT(11) NOT NULL DEFAULT '0',
`b` INT(11) NOT NULL DEFAULT '0',
`c` INT(11) NOT NULL DEFAULT '0'
PRIMARY KEY (`a`,`b`,`c`),
KEY `speed2` (`a`,`c`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

SHOW TABLE STATUS LIKE '%mytable%';
NAME ENGINE VERSION ROW_FORMAT ROWS AVG_ROW_LENGTH Data_length Max_data_length Index_length Data_free AUTO_INCREMENT Create_time Update_time Check_time COLLATION CHECKSUM Create_options COMMENT
mytable INNODB 10 COMPACT 267184317 82 21978152960 0 8147435520 7340032 2012-08-16 15:04:48 latin1_swedish_ci

The query takes quite a long time so I wanted to investigate and I found out with SHOW PROFILE that most of the time (98%) is spent in the state 'statistics'.

The 'statistics' state is used by MySQL to calculate statistics to develop a query execution plan (which indexes to use and which order to join tables). There is no JOIN in this query and I tried to 'USE INDEX(PRIMARY)' or 'FORCE INDEX(PRIMARY)' but it doesn't change anything.

So it does not make sense at all to me. Any idea why time is spent in 'statistics' state and how I could run my query faster?

Thanks

Vincent]]> Vincent B. Optimizer & Parser Tue, 28 Aug 2012 07:53:14 +0000 http://forums.mysql.com/read.php?115,565624,565624#msg-565624 STRAIGHT_JOIN for OUTER JOINGS (1 reply) http://forums.mysql.com/read.php?115,565624,565624#msg-565624
SELECT a.key, a.col_1, b.col_1 FROM (SELECT * FROM table_a ORDER BY a.key LIMIT 100) a JOIN (SELECT * FROM table_b) b ON a.key=b.key;

It turns out that using STRAIGHT_JOIN rather than INNER JOIN is much better in performance. (2x - 3x as fast)

However, what I actually need is LEFT OUTER JOIN rather than INNER JOIN. Using simply LEFT JOIN have more or less the same performance as INNER JOIN. I guess LEFT JOIN is actually using the same algorithm to INNER JOIN rather than STRAIGHT JOIN.

Is there a STRAIGHT_JOIN equivalence for OUTER JOIN?

Thanks!]]> Paul Tsui Optimizer & Parser Mon, 20 Aug 2012 11:16:01 +0000