Re: Question about the Query Cache and server settings
>SELECT * FROM some_table is just bad programming practice, though in this case, I was referring
> less to the * part and more about situations in which the programmer does not realize that how to
> use the LIMIT clause correctly, and instead retrieves boatloads of information from MySQL and then
> uses PHP to simply return, say, the first 10. This is awful practice and can lead to situations in which
> you will exceed the query_cache_limit size. Plus, it's simply inefficient; unless there is a WHERE
> clause, the only two access strategies MySQL can use are an index scan or a table scan. Neither is
> an attractive option, of course.
Ok. It looks like they're not doing that SELECT * with my slowest beast. Here's what mysqldumpslow (one of my FAV utilities btw) has for our big beast:
(this was in a shortened period of the slow_query_log -- about a 2/3rds of a day -- rather than 24 hours):
Count: 1153 Time=3.02s (3485s) Lock=0.03s (37s) Rows=9.4 (10845), user:host
SELECT t1.B_Number,t2.U_DisplayName as U_Username,t1.B_Posted,t1.B_IP,t1.B_Subject,t1.B_Body,t1.B_File,t1.B_Status,t1.B_Approved,t2.U_Picture,t1.B_Reged,t2.U_Title,t2.U_Color,t1.B_Icon,t1.B_Poll,t1.B_Parent,t2.U_Status,t2.U_Signature,t1.B_LastEdit,t1.B_LastEditBy,t2.U_Location,t2.U_TotalPosts,t2.U_Registered,t2.U_Rating,t2.U_Rates,t2.U_RealRating,t2.U_PicWidth,t2.U_PicHeight,t2.U_Number,t1.B_FileCounter,t1.B_AnonName,t1.B_ParentUser,t2.U_Birthday,t2.U_ShowBday,t1.B_AddSig
FROM w3t_Posts AS t1, w3t_Users AS t2
WHERE t1.B_Main = N
AND t1.B_PosterId = t2.U_Number
AND B_Approved = 'S'
ORDER BY B_Number
LIMIT N, N
A sample LIMIT is something like: LIMIT 40910, 10 so they're only returning a few rows. I did find one returning 40 rows. Of the above these fields have indexes on them:
w3t_users: U_Number = PK, U_DisplayName, plus a bunch more.
w3t_Posts: B_Number = PK, w3t_Postsindex3 = (B_Main, B_Board), (B_Posted, B_Board), B_PosterId, B_Approved (plus some others)...
OH! the datatypes for the fields "B_Subject" and "B_Body" are text (blobs). I can see the need for body to be a text but the subject? really they should limit ppl but hey I didn't write it and I'm supposed to leave it alone. And in w3t_Users they select the U_Signature which is also a text.
Here's the relevant show table status from db like 'w3t_Pos%'\G
Rows: 107188
Avg_row_length: 415
Data_length: 44521344
Max_data_length: 4294967295
Index_length: 8904704
I'm a bit unclear about the value in Avg_row_length - bytes? or ?? But the Data_length is telling me it's 42.5 MB. That's JUST the w3t_Posts table.
THe w3t_Users table is worse!
Rows: 237256
Avg_row_length: 557
Data_length: 132235756
Max_data_length: 4294967295
Index_length: 33201152
there are 131 rows returned when I do a desc w3t_Users!
The detail for one of these in the slow_query_log looks like this:
Query_time: 3 Lock_time: 0 Rows_sent: 5 Rows_examined: 108195
The explain for this query looks like this:
+-------+--------+-----------------------------------+-----------------+---------+---------------+-------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+--------+-----------------------------------+-----------------+---------+---------------+-------+-----------------------------+
| t1 | ref | w3t_Postsindex3,ID_ndx,B_Approved | w3t_Postsindex3 | 4 | const | 13548 | Using where; Using filesort |
| t2 | eq_ref | PRIMARY | PRIMARY | 4 | t1.B_PosterId | 1 | |
+-------+--------+-----------------------------------+-----------------+---------+---------------+-------
Of course the dreaded filesort is there.
This query is generally showing up in the slow_query_log about 2K times in a 24 hour period. It would be great is I can get this query speeded up without touching the code or schema (it's 3rd party and that will make updates difficult). The most I can do is delete redundant indexes. I've made one pass thru that and I'll make another pass thru the indexes after tweaking the server this time.
So Jay -- don't you want to work on some forums software?? :), if you know of one (even if it costs) that has a nice schema and doesn't make slow queries I'm ALL EARS! :)
thanks for your insight!
erin