CODEWITHSUNDEEP
phpmysqlsqlmysqliprepared-statement
heron
heron

Reputation: 3661

Optimizing sql statements to reduce MySQL server load

$sql1 = "SELECT questions FROM last_check_date WHERE user_id=? ORDER BY questions DESC LIMIT 1";
$sql2 = "SELECT id FROM questions WHERE add_dt>?";

What do statements above do?

When I execute sql1, it gets last check date for user.

Then I'm executing second query, to fetch all id's where add date>last check date (from sql1) and return affected rows count.

What I want to do is to merge this 2 statements into 1, and optimize query count. Following problem may occur:

There is no row for user in $sql1: must select all rows in sql2 and return affected rows count.

I can't figure out how it must look like.. Thx in advance

UPDATE

SHOW CREATE TABLE last_check_date; result is

CREATE TABLE `last_check_date` (
  `id` int(11) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `questions` datetime DEFAULT NULL,
  `users` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And SHOW CREATE TABLE questions;

CREATE TABLE `questions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `author_id` bigint(20) unsigned DEFAULT NULL,
  `question` text NOT NULL,
  `var_a` text NOT NULL,
  `var_b` text NOT NULL,
  `var_c` text NOT NULL,
  `var_d` text NOT NULL,
  `var_e` text NOT NULL,
  `subject` int(11) unsigned DEFAULT NULL,
  `chapter` int(11) unsigned DEFAULT NULL,
  `section` int(11) unsigned DEFAULT NULL,
  `paragraph` int(11) unsigned DEFAULT NULL,
  `rank` tinyint(2) NOT NULL,
  `add_dt` datetime NOT NULL,
  `answer` varchar(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_chapters-id` (`chapter`),
  KEY `fk_paragraphs-id` (`paragraph`),
  KEY `fk_subjects-id` (`subject`),
  KEY `fk_sections-id` (`section`),
  KEY `fk_author-id` (`author_id`),
  CONSTRAINT `fk_author-id` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_chapters-id` FOREIGN KEY (`chapter`) REFERENCES `chapters` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_paragraphs-id` FOREIGN KEY (`paragraph`) REFERENCES `paragraphs` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_sections-id` FOREIGN KEY (`section`) REFERENCES `sections` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_subjects-id` FOREIGN KEY (`subject`) REFERENCES `subjects` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Upvotes: 1

Views: 383

Answers (2)

Sebas
Sebas

Reputation: 21522

$sql = "
    SELECT q.id 
    FROM questions q
    LEFT JOIN (
        SELECT questions 
        FROM last_check_date 
        WHERE user_id=? 
        ORDER BY questions 
        DESC LIMIT 1
    ) l ON q.add_dt > l.questions"

$rs = mysql_query($sql);
$rowcount = mysql_num_rows($rs);

I don't know yet the proper syntax for PDO/MYSQLI, please adapt to your prefered driver.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

See below. I have assumed that if there are no records in last_check_date that you still want to show questions (in that case all of them).

select q.id
from questions q
left outer join (
    select max(questions) as questions
    from last_check_date
    where user_id = ?
) lcd on q.add_date > lcd.questions
where user_id = ?
order by questions desc

Upvotes: 1

Related Questions