maria-discuss team mailing list archive

[AskMonty KB <noreply@xxxxxxxxxxxx>]

Hello,

A new question has been asked in "Moving to MariaDB" by vignesh_sambasivamurthy_g. Please answer it at http://mariadb.com/kb/en/moving-from-oracle-to-mariadb/ as the person asking the question may not be subscribed to the mailing list.

--------------------------------
Hi,

We are conducting some POC to move our application from Oracle 11g to MariaDB 10.1.
During the testing we find that the response for the below kind of query is very slow.
Please help on below,
1. Is there any configuration that will help to make the response time better
2. Is this a know limitation/issue. If so, which release can we expect the fix

server1> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      163 |
+----------+

server1> explain select id from (select id from t1) t2 where id=1;
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id   | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|    1 | SIMPLE      | t1    | ref  | t1_idx1       | t1_idx1 | 5       | const |    1 | Using index |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

server1> explain select id from (select id, sum(id) from t1 group by id) t2 where id=1;
+------+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |  163 | Using where |
|    2 | DERIVED     | t1         | index | NULL          | t1_idx1 | 5       | NULL |  163 | Using index |
+------+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

THE ABOVE QUERY IS NOT PUSHING THE KEY FIELD TO INNER QUERY, HENCE ALL 163 RECORDS ARE BEING SELECTED RESULTING IN SLOWER RESPONSE.

Regards,
Vignesh


--------------------------------

To view or answer this question please visit: http://mariadb.com/kb/en/moving-from-oracle-to-mariadb/