0

everytime I make querys to my 40k database I use LEFT JOIN instead of adding the table to the FROM and then use AND with a condition, it's the way I learned to do it. I mean.... and this is not a real query...

I USUALLY DO THIS:

SELECT * FROM (`table_A`)
LEFT JOIN `table_B` ON `product`.`table_A` = `product`.`table_B`
WHERE product.table_A > 0

INSTEAD OF

SELECT * FROM (`table_A`, `table_B`)
WHERE product.table_A > 0
AND `product`.`table_A` = `product`.`table_B`

The fact is that every time I use LEFT JOIN in whatever query I make to the database, independently of the tables or the kind of querys, a time of 0,0012 seconds becomes 0,5 seconds or even 2-3 seconds. So, the second query is always faster than the first query using LEFT JOIN.

What's going on? Could have something to do with the memory assigned to the join_buffer_size or another parameter in MySQL?

I'm using MySQL 5.5. version. BTW, If I use 5.6 all the querys takes at least double time.

Martin
  • 22,212
  • 11
  • 70
  • 132
Alberto
  • 151
  • 1
  • 8
  • The first one is `LEFT OUTER JOIN`. Second query is `INNER JOIN` that uses old syntax. [Simple SQL Join Understanding](http://stackoverflow.com/questions/10248444/simple-sql-join-understanding). – Lukasz Szozda Apr 10 '16 at 11:38
  • Anyway your both queries are incorrect. Please provide your actual table structures, sample data and what are you trying to achieve. – Lukasz Szozda Apr 10 '16 at 11:43
  • Please first make sure that the code you post has actually correct syntax. – trincot Apr 10 '16 at 12:02
  • Sorry for the incorrect syntax, but no worries, I'm not a newbie.I wrote a basic example very fast. I'm trying to improve the performance of the website of a client, and trying to figure out why replacing every LEFT JOIN I find the queries earn no less than 0.5 seconds. No matter the query. Sometimes there are complex queries, and another times simple queries, but replacing LEFT JOINs by just adding the table to the list of tables of the query, and then the condition with AND makes every query faster. Any thoughts? Something bad in the MySQL configuration? Index seems to be ok and working BTW – Alberto Apr 10 '16 at 12:21
  • I mean, I don't want to make you lose time learning the schema of several tables, and then reading some queries trying to figure out my MySQL skills, because this happens in the same way with whatever table and whatever query. Do you understand me better now? The client's website have more than 100 tables, and at least 3 tables (like product's table) have more than 40k rows. Thanks for your thoughts! – Alberto Apr 10 '16 at 12:27

0 Answers0