Question 252185 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 1 query to select multiple rows in the same table but in different category

I have a PHP script that use 4 separate query to read the last 25 rows from one single mysql table (so a total of 100 rows).

Example:

SELECT * FROM table WHERE datetime <= 'xxx' AND name = 'john' AND category = 'cat1' ORDER BY datetime DESC LIMIT 25
SELECT * FROM table WHERE datetime <= 'xxx' AND name = 'doe' AND category = 'cat1' ORDER BY datetime DESC LIMIT 25
SELECT * FROM table WHERE datetime <= 'xxx' AND name = 'john' AND category = 'cat2' ORDER BY datetime DESC LIMIT 25
SELECT * FROM table WHERE datetime <= 'xxx' AND name = 'doe' AND category = 'cat2' ORDER BY datetime DESC LIMIT 25

I use 4 query because I have to read different records, based on: name and category, both are index. Datetime is also an index.

But this way is very slow (the table is about 60MB, 250K rows). I was wondering if this can be done with 1 single query in a faster way.

EDIT mysql table structure

CREATE TABLE IF NOT EXISTS table (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `category` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `data1` double NOT NULL,
  `data2` double NOT NULL,
  `data3` double NOT NULL,
  #....
  `data50` double NOT NULL,
  PRIMARY KEY (`id`),
  KEY `datetime` (`datetime`),
  KEY `category` (`category`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Tags:

2

Answers


  1. 0

    If you want to run the queries together you can use UNION ALL to combine the results of the queries together:

    (
    SELECT *
    FROM `table`
    WHERE datetime <= 'xxx' AND name = 'john' AND category = 'cat1'
    ORDER BY datetime DESC
    LIMIT 25
) UNION ALL (
    SELECT *
    FROM `table`
    WHERE datetime <= 'xxx' AND name = 'doe' AND category = 'cat1'
    ORDER BY datetime DESC
    LIMIT 25
) UNION ALL (
    SELECT *
    FROM `table`
    WHERE datetime <= 'xxx' AND name = 'john' AND category = 'cat2'
    ORDER BY datetime DESC
    LIMIT 25
) UNION ALL (
    SELECT *
    FROM `table`
    WHERE datetime <= 'xxx' AND name = 'doe' AND category = 'cat2'
    ORDER BY datetime DESC
    LIMIT 25
)

    Note the individual queries need to be enclosed in parentheses, due to the ORDER BY and LIMIT clauses.

    Add a composite index:

    ALTER TABLE `table` ADD INDEX (category, name, datetime);

    and you can DROP the single column index on category as it is the first column in this new index.

    Login or Signup to reply.
  2. 0

    You could do this in a single query by allocating a row number for example

    create table t(id int,category varchar(4),name varchar(20));
insert into t values
(1,'cat1','john'),(2,'cat1','john'),(3,'cat1','john'),(20,'cat1','john'),(30,'cat1','john'),
(4,'cat2','john'),(5,'cat2','john'),(6,'cat2','john'),(50,'cat2','john'),
(7,'cat1','doe'),(8,'cat1','doe'),(9,'cat1','doe'),
(10,'cat2','doe'),(11,'cat2','doe'),(12,'cat2','doe'),(120,'cat2','doe');

with cte as(
  select *,
  row_number() over (partition by name,category order by id desc) rn
  from t
  where id < 20 and name in('john','doe') and category in('cat1','cat2'))
select * from cte 
  where rn < 3
  order by name,category;

    https://dbfiddle.uk/05tdde0k

    You need to test for yourself if this is quicker than what you have

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search