Mysql Performance?

Databases
1

Hi, I’m wondering if breaking up the query into 2 or 3 queries will have any impact on the performance?

Techinically I could combine them into 1 statement but, it makes the query complicated and I suspect mysql is going through the same number of data (since the number of rows and cols in the tables is always the same regardless of the queries made).

To make things clear, heres an example:
//First statement to find a child value and store to a variable
$child = SELECT child FROM parent WHERE child = 1; //syntax is wrong, just for demostration

//THEN you find siblings with that value
$siblings = SELECT silbings FROM children WHERE child = $child

OR…

//Combining the statements
$siblings = SELECT silbings FROM children WHERE child = (SELECT child FROM parent WHERE child = 1)

Does the 2nd method performs better? It seems to me both reads the same amount of data.

2

It won’t make that much of a difference, especially if MySQL if located on he same server. Sometimes it is best to break things up just create more reusable code. This is especially true when dealing with the adjacency model. In that case your always forced to run separate queries for each node. if its for something like navigation though which is needed every request then caching after the hierarchy has been built once is a good optimization solution. Seems like what you might be attempting to do so thought I would offer up the advice.

3

Am I missing something here, or can’t you get the same thing like this:


SELECT silbings FROM children WHERE parent = 1;

I mean you already have the parent.

Or, if you mistyped your sql:


SELECT silbings FROM children WHERE parent IN (SELECT parent FROM children WHERE child = 1);

A kid can have 2 parents…

4

in general, it will make the performance worse

your child examples aren’t really very good, there’s nothing there to suggest breaking up the query (if i could understand your query)

5

sorri if the example confused you guys. it was a quick one just to demostrate what I’m trying to achieve. its not a real world example.

but I was thinking. aren’t queries kind of like filters?
Despite the WHERE statement returning only a few rows, it has to scan through the full table to select the correct data?

So I was thinking if there will actually be a speed difference between joining 2 tables together and filtering, and doing 2 small filters.

6

in general, you should not try to outthink the database optimizer

it’s a lot smarter than you think it is, and it’s way smarter than you are

no offence :slight_smile: