top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Optimizing MySQL query

+1 vote
267 views

I have a MySQL Query which has Join statement, left side of the join statement returns 1800 rows and right side is about 1500 after join I jut need top 50 rows. Total operation takes about 5 sec.
Any suggestions?

posted Oct 15, 2013 by Salil Agrawal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Try joining in two steps, say 1800 X 750 select 50 followed by 1800 X 750 select 50 union all select 50. This should reduce the operation time significantly.

answer Oct 16, 2013 by Deepankar Dubey
Similar Questions
+1 vote
Optimizing InnoDB tables in MySQL?

I was trying to optimize the InnoDB tables. I have executed the next query to detect what are the fragmented tables.

SELECT TABLE_SCHEMA,TABLE_NAME
FROM TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema","mysql") AND Data_free > 0

After that, I have seen that there are 49 fragmented tables. With one table, I have executed "optimize table table_name;" and "analyze table table_name;". The result is the same, the table continuous fragmented.

Any suggestions? I have followed the mysqltuner recommendations...

+1 vote
How to select more then three tables values in single query in MYSQL?

Joiner using how to get three tables value fetching from single query.

0 votes
Need query to determine different column definitions across tables using MySql

I'm noticing that across our several databases and hundreds of tables that column definitions are not consistent. I'm wondering if there is a tool or query (using INFORMATION_SCHEMA perhaps) that will show me all databases, tables and columns where they don't match (by column name).

For example in one table foo_id might be UNSIGNED and in other's it is not. Or maybe it's INT(11) in some and INT(10) or worse MEDIUMINT in others. Or extending further Charset/Collation might mismatch and be that stupid "latin1_swedish_ci" and fixed to be "utf8" in others.

Stuff like that. I want to see everything where there is some difference.

...