Query Optimization

[Keloran]

Im wondering when vBulletin will be optimizing its querys, i see a worrying amount of <table>.* querys, or SELECT COUNT(*)

why are there so many of these, its madness and wastes processing, if i add a plugin that adds another field to the posts table for example, then doing SELECT * is pulling that info too

what should be done is, e.g.

SELECT COUNT(postid) as postcount FROM post WHERE threadid = <threadid>

NOT

SELECT COUNT(*) as postcount FROM post WHERE threadid = <threadid>

that is just stupid, in the next version of vB (vB4) will all these problems be sorted, it would also mean, after correct index's the processing/memory requirements of vB would fall dramaticlly

[calorie]

Try turning on debug mode and looking at the queries or compare the following:

EXPLAIN SELECT COUNT(postid) AS postcount FROM post WHERE threadid = <threadid>;

EXPLAIN SELECT COUNT(*) AS postcount FROM post WHERE threadid = <threadid>;

[Keloran]

whilst with those 2 simple ones it might seem like it makes no difference, do the same query and watch your ram usage, there is a large difference, (WITHOUT THE EXPLAIN), you will see a difference, becasue the SELECT * is loading it ALL in memory, where as the SELECT postid, ONLY loads the postid, and thus is alot more optimal,

if you look at the code and read the optimization guides for MySQL you will see why doing * is a very bad idea

easy way of explaining it

i want to find how many wheels are on my car, so what ill do is take my car apart, take all the parts, and put them in piles, and then count them all up, and then take the part i want out, and do another count on that, or i could take the wheels of my car and just count them

which will take less time, less room, and be better for me ????

--------------- Added [DATE]1193301090[/DATE] at [TIME]1193301090[/TIME] ---------------

either this is being lookd into, or vB dont seem to want to admit its mistakes ??

[Marco van Herwaarden]

"SELECT *" versus "SELECT postid" makes a difference.

"SELECT COUNT(*)" versus "SELECT COUNT(postid)" however hardly makes a difference. This is already optimised by MySQL, and in both cases the only data returned is the count.

There can be many reasons why more data is pulled from the database then absolutly needed: 1 generic query versus a specific query for each process, the availability of all data to be used if needed,.....

Anyway, vB.org is not the right place to ask this. Better ask at vbulletin.com as the vB developers will see it there.