dd_gamer Posted January 20, 2012 Share Posted January 20, 2012 I'm hoping someone can help with my problem... The query (below) runs on my Localhost (laptop) but when I upload to the "live" server it can't finish without an error. The server has many many more records in it so I my code needs to be streamlined or improved... If anyone can show an example of a better way to do this query please help! I'm new to Mysql... Here is the query: SELECT DISTINCT CAST(studentrecord_daily.record_date as Date) AS academic_date, studentrecord_daily.student_id, CAST(schedule_room.scheduledate as Date) AS schedule_date, rc.rc_period_id, rc.start_date, rc.end_date, schedules.id, schedules.gf, schedules.schedule_desc, schedule_room.scheduleid FROM schedule_room, studentrecord_daily, rc, schedules WHERE studentrecord_daily.student_id = '3' AND rc.rc_period_id = '27' AND studentrecord_daily.record_date >= rc.start_date AND studentrecord_daily.record_date <= rc.end_date AND CAST(studentrecord_daily.record_date as Date) = CAST(schedule_room.scheduledate as Date) AND schedules.id =schedule_room.scheduleid Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted January 20, 2012 Share Posted January 20, 2012 have you tried running this in your servers phpmyadmin? what error(s) exactly do you receive? Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 20, 2012 Author Share Posted January 20, 2012 The error is a standard Server 500 error and the page keeps processing until "timeout"! Not sure how to run query in phpMyAdmin... Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted January 20, 2012 Share Posted January 20, 2012 The error is a standard Server 500 error and the page keeps processing until "timeout"! Not sure how to run query in phpMyAdmin... click the SQL button on the top nav of the interface, and paste your query in it.. this may not be a query issue, what other code do you have on the page in question? Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 20, 2012 Author Share Posted January 20, 2012 Thanks! I did what you said and used phpmyAdmin... the query took too long... before timeout so I increased the time limit and it took 90 seconds to run but it did run on my Local machine! The "live" server has many more records for the query to search so I'm wondering what are my options are because it will time out! Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted January 20, 2012 Share Posted January 20, 2012 Thanks! I did what you said and used phpmyAdmin... the query took too long... before timeout so I increased the time limit and it took 90 seconds to run but it did run on my Local machine! The "live" server has many more records for the query to search so I'm wondering what are my options are because it will time out! if you have a large result set, my assumption would be that the query is taking so long because it has to cast two fields every iteration, why no set scheduledate and record_date to the DATE format in your db table, which is a good practice anyway, that will eliminate the need to cast them entirely. Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 20, 2012 Author Share Posted January 20, 2012 The dates are in the database as follows: 1) studentrecord_daily.record_date = 2011-10-07 11:06:04 2) rc.start_date, rc.end_date, = 2009-09-07 00:00:00 3) scheduledate = 2000-11-14 00:00:00 You can see that #1 has a time logged... I'm not sure what you are saying! Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 20, 2012 Author Share Posted January 20, 2012 Fyi, I didn't understand your comment : why no set scheduledate and record_date to the DATE format in your db table, which is a good practice anyway, that will eliminate the need to cast them entirely. All dates in all tables are Type: varchar(255). I didn't create the database just making the report. I need to compare the dates and I used CAST so the time won't create a problem when compared. I hope this helps explain why I'm using CAST. Quote Link to comment Share on other sites More sharing options...
Psycho Posted January 20, 2012 Share Posted January 20, 2012 There are inefficiencies in that query. 1. Why are you using CAST() on the date value in the select value? All values returned from a MySQL query are treated as text anyway. It's only useful to cast input values to insert into certain data type fields or to compare values that are of different types 2. Why are you using CAST() on the date field in the WHERE caluse. Are they of different types? If not, don't use CAST(). 3. Not sure, but putting all your tables as a list and then trying to do the JOINs in the WHERE clauses might be problematic too. Here is an attempt at revising your query, but I really have no way of knowing if it will work SELECT sr.scheduleid, CAST(sr.scheduledate as Date) AS schedule_date, s.id, s.gf, s.schedule_desc, DISTINCT srd.record_date AS academic_date, srd.student_id, rc.rc_period_id, rc.start_date, rc.end_date, FROM schedule_room AS sr JOIN schedules AS s ON s.id =sr.scheduleid JOIN studentrecord_daily AS srd ON srd.record_date = sr.scheduledate JOIN rc ON srd.record_date BETWEEN rc.start_date AND rc.end_date WHERE srd.student_id = '3' AND rc.rc_period_id = '27' Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 21, 2012 Author Share Posted January 21, 2012 Hopefully I can explain what's going on and why I'm using CAST. If I don't need to use it then I won't but when I take it out I get "zero" results. Thank you for the input "Psycho". After your input I removed the CAST from CAST(schedule_room.scheduledate as Date) AS schedule_date, From both the SELECT and WHERE Clause... I got the same results so this CAST was not needed for this table. The time it took to process didn't improve more much if any. The reason I need the CAST for the CAST(studentrecord_daily.record_date as Date) AS academic_date, is because this record has a time in the record '2011-09-06 15:17:15' and has multiple times using the same date. I only want to check what dates they login and because they login many times in the same day I need to remove that from the results. Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 21, 2012 Author Share Posted January 21, 2012 OUTPUT academic_date student_id schedule_date rc_period_id Start Date End Date 2011-09-06 2157 2011-09-06 00:00:00 69 2011-09-06 2011-11-04 2011-09-07 2157 2011-09-07 00:00:00 69 2011-09-06 2011-11-04 2011-09-08 2157 2011-09-08 00:00:00 69 2011-09-06 2011-11-04 2011-09-09 2157 2011-09-09 00:00:00 69 2011-09-06 2011-11-04 2011-09-13 2157 2011-09-13 00:00:00 69 2011-09-06 2011-11-04 By using CAST on academic_date I have removed the "time" element... Quote Link to comment Share on other sites More sharing options...
Pikachu2000 Posted January 21, 2012 Share Posted January 21, 2012 I'm really not sure if it's any more efficient, but instead of using CAST(field AS DATE), you can use DATE(field). I suspect it may be quicker since it just extracts a portion of the field, but you could try it and benchmark it if you're so inclined. Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 21, 2012 Author Share Posted January 21, 2012 Thank you Pikachu2000 I have tested it and the results are the same Thanks for the tip! I haven't been able to figure out the Join... Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 23, 2012 Author Share Posted January 23, 2012 I tried to modify my code using joins and the DATE method. I also changed the script so I don't use the rc table for now: SELECT DISTINCT DATE(studentrecord_daily.record_date), studentrecord_daily.student_id, schedule_room.scheduledate, schedule_room.scheduleid, schedules.id, schedules.gf, schedules.schedule_desc FROM schedule_room JOIN schedules ON schedules.id = schedule_room.scheduleid JOIN studentrecord_daily ON DATE(studentrecord_daily.record_date ) = schedule_room.scheduledate WHERE studentrecord_daily.student_id = '3' AND studentrecord_daily.record_date >= '2011-09-06' AND studentrecord_daily.record_date <= '2011-11-04' But this didn't change the fact it took over 90 seconds to run... Showing rows 0 - 23 (24 total, Query took 91.2779 sec) I must be missing something here... currently it would take over 1 hour to look up 30 students records..ahh! Any ideas? Quote Link to comment Share on other sites More sharing options...
Psycho Posted January 23, 2012 Share Posted January 23, 2012 Are you running this query in PHPMyAdmin (or something similar) or are you running it through PHP code. If only through PHP try running it in your database management app. Also, a couple other things: 1. Try changing the WHERE clause for the two date checks to a BETWEEN clause instead of two separate checks. 2. Try indexing the columns that you are using in your WHERE and JOIN clauses. Lastly, you should run your query as an "EXPLAIN" query. Preface the select with "EXPLAIN". The output gives details about the efficiency of your query that you can improve upon. http://dev.mysql.com/doc/refman/5.1/en/using-explain.html Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 24, 2012 Author Share Posted January 24, 2012 I did try the "BETWEEN" and using phpMyadmin interface and only gained "1" second. I did read about the Explained and the follow report shows my issues from the query. | TABLE | type | possible_keys | key |key_len | ref | rows | Extra ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | schedule_room | ALL | NULL | NULL | NULL | NULL | 39168 | Using temporary | schedules | eq_ref | PRIMARY | PRIMARY | 4 |schedule_room.scheduleid | 1 | | studentrecord_daily | ALL | NULL | NULL | NULL | NULL | 179812 | Using where; Using join buffer I'm not sure how to "Index" a table that has another field indexed... Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 24, 2012 Author Share Posted January 24, 2012 | TABLE | type | possible_keys | key |key_len | ref | rows | Extra ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | schedule_room | ALL | NULL | NULL | NULL | NULL | 39168 | Using temporary | schedules | eq_ref | PRIMARY | PRIMARY | 4 |schedule_room.scheduleid | 1 | | studentrecord_daily | ALL | NULL | NULL | NULL | NULL | 179812 | Using where; Using join buffer I'm re-posting the EXPLAINED so that its readable. My question is how do the Index say the studentrecord_daily table for this query when it already has an "id" that is auto_increment? Or does that matter? Quote Link to comment Share on other sites More sharing options...
Psycho Posted January 24, 2012 Share Posted January 24, 2012 You can index ANY column in your table. Indexing a column makes it faster when querying with that data where the MySQL engine has to do comparisons against those columns (i.e. in WHERE clauses, joins, etc.). Your primary key fields (i.e. auto-increments) are typically indexed by default. And you should index the foreign keys as well so doing JOINS are quicker. But, don't needlessly index columns that aren't used much for comparisons. To index additional columns in a table, go to the table in PHPMyAdmin and select the "Structure" tab. You should have two grids on the page. The top grid shows all the fields in the table with a list of properties and actions available for each. The bottom grid shows all the indexed fields. To index a field, find it in the top grid and on the very right-hand side you should see an icon of a table with a lightning bolt on it. Click it and that field will be indexed. However, I'm curious why your queries are taking so long. Unless you have a ton of records, 90 seconds is way too long. Are you sure you are using the correct field types for your data? Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 24, 2012 Author Share Posted January 24, 2012 Thank you for the index info... I was reading that it could increase speed to change the type from the EXPLAINED report because "ALL" is the worst case. The number of record's in the studentrecord_daily is 179,812 rows currently. I just checked the datatypes (schedule_room.scheduledate, studentrecord_daily.record_date) and the fields I'm working with are varchar(255)... I'm assuming that's not good!! Quote Link to comment Share on other sites More sharing options...
fenway Posted January 24, 2012 Share Posted January 24, 2012 You need an index on schedule_room.scheduleid, and on studentrecord_daily.student_id, at the very least. And of course, fix the column types. Quote Link to comment Share on other sites More sharing options...
Psycho Posted January 24, 2012 Share Posted January 24, 2012 I just checked the datatypes (schedule_room.scheduledate, studentrecord_daily.record_date) and the fields I'm working with are varchar(255)... I'm assuming that's not good!! Um, yeah. That is why I asked back on my first post why you were using CAST() to cast fields into DATE types when they should already have been date types. That is most likely the cause of all your problems. If you want MySQL to do comparisons of data (for example checking if a date is greater or less than another date) then MySQL needs those objects to be dates. Instead MySQL is having to convert ALL those fields into types it can work with THEN do the comparison. In addition to changing your date fields to date types I hope you are using INT types for the ID fields. Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 24, 2012 Author Share Posted January 24, 2012 Thanks Psycho and fenway! Changed the schedule_room.scheduledate field from Varchar to DATE CAST(schedule_room.scheduledate as Date) AS schedule_date, Query took 2.6 seconds... Live and learn! Quote Link to comment Share on other sites More sharing options...
Psycho Posted January 24, 2012 Share Posted January 24, 2012 CAST(schedule_room.scheduledate as Date) AS schedule_date, You are not still using the CAST() are you? Quote Link to comment Share on other sites More sharing options...
dd_gamer Posted January 24, 2012 Author Share Posted January 24, 2012 Nope! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.