Hot answers tagged

3

when you're using the "limit 48 offset 7776" mysql need to scan all rows(48+7776), along with offset number increase, mysql need to scan more and more rows for "Next page" To improve this, try: select ... from ... where id > 7776 limit 48 or try: select ... from ... where id between 7776 and 7824


2

A unique constraint on TEACHER_ID, STUDENT_ID would prevent duplicate STUDENT_ID for a teacher. In fact you could just make the the PK and drop ID. This is TSQL so it may be wrong for mysql SELECT S.ID , S.LASTNAME , S.FIRSTNAME FROM STUDENTS S LEFT JOIN TEACHERS T ON T.STUDENT_ID = S.ID AND T.TEACHER_ID = $teacherID ...


2

Remember where you "left off". That way the "Next" page can do WHERE id > $leftoff ORDER BY id LIMIT 10 very efficiently. "Prev" page can be done similarly, as can "First" and "Last". More discussion in my blog.


1

Your join should be on TEACHER_ID = STUDENTS.ID and not the ID of the TEACHERS table. Your WHERE clause should also use TEACHER_ID. See if the below works for you. SELECT S.ID , S.LASTNAME , S.FIRSTNAME FROM STUDENTS S LEFT OUTER JOIN TEACHERS T ON T.STUDENT_ID = S.ID WHERE TEACHERS.TEACHER_ID <> $teacherID


1

"Global" settings are initial values for when you login. You failed to log out and log back in, hence it still said STATEMENT. /etc/ is a place for configuration-like stuff, not for basedir. /usr does not seem right for basedir, either. Perhaps /usr/mysql or /usr/mariadb?


1

INDEX(a) is unnecessary when you also have INDEX(a,b). So get rid of these: KEY `company_id` (`company_id`), KEY `ca` (`company_id`,`account_number`), ca is a good index, but cab is better; removing ca will encourage the optimizer to use cab. How many rows are in the result set? Was the SELECT slow during one of those big DELETE + INSERT times? If so, ...


1

See if this meets your specs: SELECT SUM(turned_on), SUM(turned_off) FROM ( SELECT ( @prev <= 5 AND EngineOil_P > 5 ) turned_on, ( @prev >= 15 AND EngineOil_P < 15 ) turned_off, @prev := EngineOil_P FROM ( SELECT @prev := 0 ) init JOIN tbl ORDER BY ...



Only top voted, non community-wiki answers of a minimum length are eligible