Speed of member profiles

[Dani]

I have fixed the bug that was causing the ridiculous slowdown when viewing member profiles. It turns out that a particular WHERE clause in one of the queries was slowing things down too much, and after catching the culpreit, I was able to perform the query in PHP instead.

[Dani]

The speed of finding unanswered threads has also been rectified, unfortunately at the cost of showing the results as threads instead of as posts.

[~s.o.s~]

Hmm.. so was it the number of round trips made to the database or a logic problem ?

[Dani]

Neither, actually. I had an SQL statement of the type:

SELECT * from table
WHERE
     column <> 123
     AND FIND_IN_SET(column, $php_array)

For whatever reason, it was taking a ridiculously long amount of time, despite having a MySQL index on the table column. So what I went and did was remove the first WHERE clause and instead sanitized the $php_array array to never include the element 123 before the sql query even runs. (I needed $php_array to include element 123 elsewhere in the script except for this particular SQL query which is why I did it that way to begin with.)

[~s.o.s~]

Okay so it was like 'performing operations which should never have been perfomed by the sql' sort of thing.

BTW indexing doesn't account for bad code you know...;)

[Dani]

Don't pick on me :) MySQL had to examine COLUMN anyways. It's not my fault it did a brute force search on all of the rows twice just because I wanted to compare COLUMN against a variable and an array.

[~s.o.s~]

I certainly don't think it would be traversing the table twice, but I guess you know your tools the best.

BTW why not just use a simple IN clause ?

[Dani]

I'm thinking it would traverse all rows eliminating those where column is 123, and then go back on all of the others and check whether the value of column is in the array, so essentially that's going over nearly everything twice, no?

Well I did use a FIND_IN_SET() clause?

[~s.o.s~]

I'm thinking it would traverse all rows eliminating those where column is 123, and then go back on all of the others and check whether the value of column is in the array, so essentially that's going over nearly everything twice, no?

So you mean to say that every condition put in the WHERE clause results in a full table scan.. ? Atleast not in Oracle.

So if I were to use:

select * from Emp 
where (emp_name LIKE '%Dani%') AND
        (emp_id IN (1, 2, 3, 4, 5)) AND
        (sal > 10000) ;

would this query scan entire table containing millions of records thrice ? Logically, don't think so....

Well I did use a FIND_IN_SET() clause?

Is there no simple IN clause in MySQL ? Something like the one written by me in the above snippet ?

[Dani]

FIND_IN_SET() appears to be similar to your IN() clause. However, you have to understand that this whole query is meant to determine which a member's favorite forums are and other members interested in the same forums. Therefore, we're searching 300,000+ rows first to see if one column isn't equal to a certain value (cuts out about 10%) and then seeing if the column value is in an array (searching the remaining 90%).

[Dani]

Have switched over one of the instances where I was using FIND_IN_SET() to IN(). Hasn't seemed to make any difference. Best I can tell the difference between the two functions is that FIND_IN_SET() returns the position of where it found the value.

[Dani]

Oh ... FIND_IN_SET also lets me work backwards. For example FIND_IN_SET($php_variable, COLUMN_OF_COMMA_DELIMITED_LISTS) which I used elsewhere. Never saw IN() used in this fashion before although I have no idea whether it can be.

[~s.o.s~]

I suppose even IN can be used in the fashion you have mentioned, since Oracle doesn't have a FIND_IN_SET function.

Oh and btw, the IN clause in Oracle is really poweful since it works with literally any type of data.

select '~s.o.s~' from dual
where to_date('01/02/99', 'dd/mm/yy') in ( to_date('01/02/99','dd/mm/yy')) ;

Can MySQL do that... :P

[Dani]

Yup, MySQL has lots of built-in functions. For example, here are some functions to manipulate dates and times:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

MySQL also support subqueries almost anywhere. For example

SELECT * FROM table1
WHERE value = (SELECT c1 FROM table2 WHERE c2 = 'a' LIMIT 1)

Sorta a roundabout way of doing a join but you get the idea.

[jbennet]

can you do that on MSSQL?
if so that would help me a great deal

[Dani]

No idea.