-1

I am new in programming. A have a query which sometimes works but sometimes doesn't works at all. I mean if the result is not only a row it become too slow. If the result is several hundred rows it doesn't works at all. I know it's not the best to use 'like' in the query but I have no other options (or I just don't know). Please help me to optimize my query. I should run this query with different "where" options (the first part would be always the same). Shall I store the first part in a function? Does it make the query faster? 

SELECT a.*,
       group_concat(DISTINCT concat(' ', b.name)),
       group_concat(DISTINCT concat(' ', e.name)),
       group_concat(DISTINCT concat(' ', g.name))
FROM t1 a
     LEFT JOIN t2 c
     INNER JOIN t3 b ON c.id = b.id ON a.id = c.id
     LEFT JOIN t4 d
     INNER JOIN t5 e ON e.code = d.code ON a.id = d.id
     LEFT JOIN t6 f
     INNER JOIN t7 g ON g.code = f.code ON a.id = f.id
WHERE a.x LIKE '%5%'
GROUP BY a.id;

Thank you in advance!

leftjoin
  • 36,950
  • 8
  • 57
  • 116
user6265779
  • 49
  • 2
  • 1
    We need to see your table and index definitions to know if they've been created correctly. If you haven't created indexes, that's almost definitely your problem, and you should start reading about indexes at http://use-the-index-luke.com – Andy Lester Oct 04 '17 at 13:55
  • 1
    Please edit your question to show sample input and expected output, and indexes. Also, "slow" is subjective. And you already know part of your issue (using "like"). And the fact you're doing multiple joins. – David Makogon Oct 04 '17 at 13:56
  • create indexes for fields which you use in where condition , but first check the explain plan object – Frank Oct 04 '17 at 13:56
  • 1
    This kind of problem is symptomatic of poor design – Strawberry Oct 04 '17 at 13:56
  • Firstly, in your query outer joins are useless as you later attempt to `inner join` those tables, which will remove NULL values from the result set anyway. Secondly, run `EXPLAIN` for your query and post its (formatted) output, along with the table and index definitions. – mustaccio Oct 04 '17 at 14:00
  • 1
    Most likely because you're selecting all from table a and you dont specify the on clause in your left joins – Ryan Gadsdon Oct 04 '17 at 14:04
  • INNER JOIN t3 b ON c.id = b.id ON a.id = c.id - surely not? – P.Salmon Oct 04 '17 at 15:10
  • 1
    It is a flaw in MySQL that it let's slip this incorrect syntax. In standard SQL an outer join needs an `ON` or `USING` clause to specify on which criteria to join. If you want a join without criteria (i.e. combine every record from one table with every record in the other table) use `CROSS JOIN` instead. MySQL silently converts your `LEFT JOIN` to `CROSS JOIN` instead of throwing a syntax error exception. Let's say you get 1000 records from each table a, c, d, and f; that makes a result of 1000^4 = a trillion records. – Thorsten Kettner Oct 04 '17 at 15:12
  • 1
    'Fixing' the left joins may be the way to go then. – P.Salmon Oct 04 '17 at 15:16
  • you can see in this site for further explanation https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ @user6265779 – Gagantous Oct 04 '17 at 15:28
  • 1
    @AndyLester thank you very much for your comment and the link. You were right, I made a terrible mistake not to use the indexes correctly. Now I set new indexes and it works very well. My queries are much more faster, as well. Maybe my joins are not the preferred ones but I have no idea, what to use instead of left joins. – user6265779 Oct 05 '17 at 09:45
  • 1
    Why are you using left joins without 'ON' clause? – leftjoin Oct 05 '17 at 13:28
  • Code with no specification can't be debugged & buggy code can't be "optimized". [mre] [ask] [help] PS LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. A faq. Eg [Re INNER JOIN after OUTER JOIN invalidating OUTER JOIN](https://stackoverflow.com/q/55094277/3404097) – philipxy Dec 05 '21 at 01:07
  • PS Also this seems likely to suffer from another common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. Write separate aggregate subqueries over appropriate rows and/or aggregate (maybe distinct) case statements picking rows of joins; then join the subqueries together. Another faq. Eg [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) – philipxy Dec 05 '21 at 01:09

2 Answers2

4

A join without condition is a cross join. In your query there are three such joins: t2, t4, t6 tables. A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables. It will produce rows which combine each row from the first table with each row from the second table. The performance is poor because cross join produces too many rows. See documentation: https://www.w3resource.com/sql/joins/cross-join.php

leftjoin
  • 36,950
  • 8
  • 57
  • 116
-4

if a.x is a varchar field use something like

Full-Text Index

I 'm not sure if mysql has it but give it a try This is what I use in SQL Server

Hakan Hifzioglu
  • 35
  • 5