[RESOLVED] Does a SELECT * slow it down ?

MichaelHe

I get some data from a table like this:

$getlist=mysql_query("SELECT * FROM Kunder WHERE 1 $opt1 $opt2 $opt3 $opt4 ORDER BY Vip DESC, WogE DESC, KundeNavn");
while ($row = mysql_fetch_array($getlist)){
$kunid=$row['KunID'];
$kundenavn=$row['KundeNavn'];
$postnummer=$row['Postnummer'];
$by=$row['By'];
$tlf=$row['Tlf'];
$email=$row['Email'];
$web=$row['Web'];
$lydklip=$row['Lydklip'];
$gbog=$row['Gbog'];
$vip=$row['Vip'];
$woge=$row['WogE'];
$skjulemail=$row['SkjulEmail'];
$kbeskrivelse=$row['KBeskrivelse'];
$kbeskrivelse = nl2br($kbeskrivelse);

But it is sometimes very slow when I go to the site.

Could it be because I write SELECT
The table has many fields and as you can see I only use 13 of them in this list.
But is it slowing it down when I write ??
I mean if it is getting all the data from the table, instead of just getting the 13 I need, or is it not something that should slow it down ?

ClarkF1

I'm not sure what "WHERE 1 $opt1 $opt2 $opt3 $opt4 " is for.

Normally you have something like "WHERE id = '$id'"

Only selecting the data you need from the table does speed things up if you've got a large table

bradgrafelman

Yes, SELECT * is more costly than just selecting the fields you're going to use (unless you're positive you're going to use all of the fields).

Try echo'ing your query string out so we can visually inspect it.

MarkR

"SELECT *" may cause the database to return more data than you require, but how much it affects it really depends on the values of those other columns.

If you have a 100k blob in each row you're not using, then it's a really good idea not to use "SELECT *".

If you have a few relatively normal columns, "SELECT " is fine. Moreover, "SELECT " is a very nifty shortcut.

If you find that you're often doing "SELECT some big column list FROM x JOIN y on SOMETHING ...", you may want to create a view.

Views are like saved queries in the database. They take up (almost) no space and don't affect the performance of the query. They just make the code more maintainable (If used sensibly).

Mark

MichaelHe

Hi everyone.

The $opt1,2,3 and 4 is put in by a form drop down menu... A sort of filter.. When the filter isn't being set to anything, these parameters are empty.

How do I echo my query string exactly ?

I have always used the because my databases hasn't been very large until now. So I'm a little new to not using
If I need the same variable as in my example like KunID row is put in a $kunid and KundeNavn is put in $kundenavn and so on...
Do I then just write SELECT KunID as kunid, KundeNavn as kundenavn, and so on.. and do I still need the $kunid=$row['KunID']; afterwards ?

by the way I have about 39 fields in the table and there are some INT(4) some varchar(50) and (30) some text fields and a date field.
All in all the 361 entries in the table uses 190.104 bytes.
Just to tell you how much and how large the database is.

Thanks a lot for your help
Michael

ClarkF1

MichaelHe wrote:
How do I echo my query string exactly ?

You store it in a variable first e.g.

 $query = "SELECT * FROM Kunder WHERE 1 $opt1 $opt2 $opt3 $opt4 ORDER BY Vip DESC, WogE DESC, KundeNavn"; 
echo $query; //you then remove or comment this line when the script goes public
 $getlist=mysql_query($query);

I do this for all my queries in case there's a problem.

Piranha

A table that only contains a few rows (such as yours) should not be slow if you don't have something very special in it. In your case it should be no problem unless you don't have a very special condition in your query, but I doubt it.

There are however lots of other reasons why it can be slow. As examples can be mentioned that the computer might work with something else, that you handle and output much to the browser or simply that the computer is slow.

To see where the problem is I think you should echo microtime to see how long the different things take:

$time = microtime();
// Run the query
echo microtime - $time;
// Loop through the result
echo microtome - $time;
// And so on

Then you can see what parts that take lots of time and what parts you don't have to worry about. Just be sure to run it several times and use some kind of mean to get accurate results.

MichaelHe

My complete query looks like this

SELECT * FROM Valg v1
JOIN Valg v2 ON (v1.KunID = v2.KunID)
JOIN Valg v3 ON (v2.KunID = v3.KunID)
JOIN Valg v4 ON (v3.KunID = v4.KunID)
LEFT JOIN Kunder ON (v1.KunID = Kunder.KundeID)
WHERE 1 $opt1 $opt2 $opt3 $opt4 AND Branche='$branche' AND EmailValid='x'
GROUP BY v1.KunID
ORDER BY Kunder.Vip DESC, Kunder.WogE DESC, Kunder.KundeNavn

And I can see that it takes 17 seconds to do this query in MyPhpAdmin

If I remove all the JOIN and only select * from Kunder it takes no time at all.
So it's the JOINs that takes a lot of time.

In my Valg table I have 2245 rows and 3 INT fields with a index on all of them.

Is there any way I can make this Valg table faster ?

Piranha

First to the original question: Loose the SELECT * and only return what you need.

Second, we still don't have a clue what the $opt is. Please post the query as it is sent to the database.

Third, why do you join with Valg 4 times and not only once?

NogDog

Another advantage to explicitly listing the columns to be selected (instead of using "") is that if there is a change to the database, then your select statements will immediately start throwing syntax errors if a column name that you are expecting to use either no longer exists or has had its name changed for some reason. If you use "", then such side-effects may be harder to track down. Also, if you use mysql_fetch_array and refer to result columns via their numeric indexes, if there is a change in the order of the columns in a table, that can throw off your query result processing causing yet another late-night debugging session to figure out why.

MichaelHe

Hi everyone.

I think I have solved the problem..

It's a little complecated.

I have a table with profiles and their data called Kunder (costumers in Danish)
Then I have a table with a lot of choices.
And at last I have a table with all the choices each profil has checkmarked called Valg (choices in Danish)

When a costumers (ex. costumer ID 1) checkmarkes ex. choice number 5 it puts data in the table Valg with KundeID 1 and Valg 5
So if a costumer has checkmarked 5 choices there are 5 rows in the Valg table called
1 5
1 7
1 2
1 9
1 15
ex.

At the top of the page I then have a filter so when I choose the filter 5 it only shows the costumers who has checkmarked choise number 5.
But the tricky part is that I have 4 filters. So if I choose ex. 5, 7, 9 and 15 it has to check the Valg table and find the costumers who has checkmarked all 4 choices.

Therefore I have it like this:
SELECT * FROM Valg v1
JOIN Valg v2 ON (v1.KunID = v2.KunID)
JOIN Valg v3 ON (v1.KunID = v3.KunID)
JOIN Valg v4 ON (v1.KunID = v4.KunID)
LEFT JOIN Kunder ON (v1.KunID = Kunder.KundeID)

The $opt1-4 is then something like this
$opt1 = " AND v1.Choice='1' "
$opt2 = " AND v2.Choice='4' "
$opt3 = " AND v3.Choice='8' "
$opt4 = " AND v4.Choice='13' "

so the final query could looks like this ex. if all 4 filters is in use :

SELECT * FROM Valg v1
JOIN Valg v2 ON (v1.KunID = v2.KunID)
JOIN Valg v3 ON (v1.KunID = v3.KunID)
JOIN Valg v4 ON (v1.KunID = v4.KunID)
LEFT JOIN Kunder ON (v1.KunID = Kunder.KundeID)
WHERE 1 AND v1.Choice='1'
AND v2.Choice='4'
AND v3.Choice='8'
AND v4.Choice='13'
AND Branche='Musik'
AND EmailValid='x'
GROUP BY v1.KunID
ORDER BY Kunder.Vip DESC, Kunder.WogE DESC, Kunder.KundeNavn

You can see the funktion here, though it's in Danish.
http://www.festfagbogen.dk/Liste.php?Branche=Musik

The problem with it is that when no filters are in use when you enter the site, the query makes about 758.000 rows because it prints out every possible way the checkmarks for ex, costumer 1 can be combined. If costumer 1 has checkmarked 11 choices it gets 9x9x9x9=6561 rows.
And with about 100 costumers in this category it makes 758.000 rows which it has to GROUP so each costumer is only shown ones and order them afterward.
This took 17 seconds.

But now I have done it like this.

IF ($valg2>''){$join2='JOIN Valg v2 ON (v1.KunID = v2.KunID)';}
IF ($valg3>''){$join3='JOIN Valg v3 ON (v1.KunID = v3.KunID)';}
IF ($valg4>''){$join4='JOIN Valg v4 ON (v1.KunID = v4.KunID)';}

$getlist=mysql_query("
SELECT * FROM Valg v1
$join2
$join3
$join4
LEFT JOIN Kunder ON (v1.KunID = Kunder.KundeID)
WHERE 1 $opt1 $opt2 $opt3 $opt4 AND Branche='$branche' AND EmailValid='x'
GROUP BY v1.KunID
ORDER BY Kunder.Vip DESC, Kunder.WogE DESC, Kunder.KundeNavn

This way when you enter the site and no filters are in use, it doesn't add the Valg 4 times in the query and it takes no time at all.
But when you take 2 or more filters in use it adds JOINs in the query.
When all the filters are in use it can max be 4x4x4x4=256 rows per costumer and when all 4 filters are in use there are only 10-20 left of the 100 costumers, so it will make max 256x20=5120 rows.
That's a lot less than 758.000 and now there has to be 3000 costumers matching all 4 filters for it to take 17 second, and there are only 100 in the largest category, so it's safe for now :o)

Well it was a long explanation, but all your comments halped me figure out where the problem was, and I got it solved :o)

Thanks a lot
Michael

Piranha

Good that it is solved. But next time please do not try to hold back information, like joining with 4 tables and how the different $opt look like. It make it more or less impossible to see what the error is. Also getting people to help you out while it is impossible will remember it if it happens to often and not assist you.

Please do not take this the wrong way. Just give enough information and people will help you out.

MichaelHe

I totally understand you :o)

I hadn't thought that the JOIN would make a different in the speed, so I thought that it would mean more explanation if I wrote them. So I simplified it...

But now I know till next time... :o)

Piranha

The two things that takes longest to do in a relational database is to join tables and to order the result. Using indexes significantly lowers the time these things take, but it still take a lot of time. Just to let you know until next time 🙂