MySQL query is making the database go down

Hi,

I have this query when a user runs it, it makes the database go down, and wonder what's wrong with it or how I can optimize it.


select o.*,c.*,r.name,s.* from orders o inner join (users r,customer c, order_status s) on (r.user_id = o.user_id and o.customer_id = c.customer_id and s.status_id = o.status_id) and r.user_id = ? and c.forname like ?or c.aftername like ? ORDER BY created_at DESC


and this is how a query with input can look like


select o.*,c.*,r.name,s.* from orders o inner join (users r,customer c, order_status s) on (r.user_id = o.user_id and o.customer_id = c.customer_id and s.status_id = o.status_id) and r.user_id = 697 and c.forname like '%Svensson%'or c.aftername like '%Svensson%' ORDER BY created_at DESC

 

First, pls provide DB structure, how many rows in table.
Pls provide explain information + you can used MySQL profiling and check which operation have biggest execution time.

About ORDER BY - you need to know how mysql optimize this constrcution. If you use ORDER BY created_at you need check in EXPLAIN report. It's must be field from the first table which you see in this report. No matter how you write your MySQL query, internal MySQL optmizator use this tables in that order which he need. If this table not on the first row, all data which will be join must copy to temporary DB table - it's very bad for big datasets.

p.s. Sorry for my bad English

 

Pls provide more information about DB structure, how many rows in tables, explain report + profiling report.

Then i can try to help you

 

Select Student.id, Student.name, Schoold.Adress, District.SchoolID from Student inner join Schoold on Student.StudentID=Schoold.StudentID inner join District on School.Id=District.SchoolId order by Student.id asc

 

@aftsanisav, didn't really understand what you mean, do you want me to look for anything in the phpmyadmin?

@noorlinks, how would that change my query? I'm not that good in sql queries, i would appreciate if you could give the example with my query.

Thanks in advance

 

1 - I need your DB structures
2 - You need execute EXPLAIN here_you_query and provide this report here
3 - You need use Profiling in Phpmyamdin and send this report here.

If you want really optimized query. Then i can provide to you good recomendations

 

1 - I don't know if there are any actual structur of the database
2 - this i dont understans?
3 - How do I use the Profiling in phpmyadmin?

 

1 - you need use Export in phpmadmin for that tables which used in your query
2 - In phpmyadmin, where you enter your query you need execute - EXPLAIN your_query and then you will see some table, which you need provide to me.
3 - Profiling - read about this in google

 

Looks like your code is full of errors.... I dont even have to start looking at your architecture. I was about to try and debug it for you...then realised it was just nuts.

select o.*,c.*,r.name,s.* from orders o inner join (users r,customer c, order_status s) on (r.user_id = o.user_id and o.customer_id = c.customer_id and s.status_id = o.status_id) and r.user_id = 697 and c.forname like '%Svensson%'or c.aftername like '%Svensson%' ORDER BY created_at DESC

 

yeah i know it's the worse sql query ever but i don't know how i can correct it, because when i run that query the database get's locked and no one can enter the website anymore.

 

Yeah, but you need provide dump of your tables structure + some rows for tests. I cannot help you without this. If you can do this will be great. Without this information i cannot provide correct + optimized answer

 

Yer, just give us a bit more detail and one of us will write the query for you.

 

SELECT DB.o.*
,DB.c.*
,DB.r.name
,DB.s.*
FROM DB.O INNER JOIN
DB.C ON
DB.O.Commonfield=DB.C.Commonfield INNER JOIN
DB.R On
DB.R.Commonfield=DB.C.Commonfield INNER JOIN
DB.S ON
DB.S.Commonfield=DB.R.Commonfield
WHERE DB.r.user_id = 697
AND DB.c.forname like '%Svensson%'
OR DB.c.aftername like '%Svensson%'
ORDER BY created_at DESC

Right...well. I've tried my best. Where i've put DB - you will have to write the name of the database that the particular table is in. Where I have put commonfield, you will need to type in the name of the field which you want to match on. For example if O has a field that matches to a field in C, then put those field names in.

If S and R link to O rather than to C or S respectively then just change the letters in the joins.

Added some formatting to make it readable.

Hope it helps!