0

Im experimenting with query speed improvements and have been unable to improve my OR statement with a UNION ALL. Im not sure if its a config problem or just that union all is not applicable to my current situation. Any suggestions or comments are greatly appreciated.

Q1

SELECT * from students where `year` = 'SENIOR' or `year` = 'FRESHMEN'

This query takes ~.6s and runs on 250227 rows Q2

SELECT * from students where `year` = 'SENIOR'
union all
select * from students where `year` = 'FRESHMEN'

This query takes ~1.2s and runs on 250227 rows. Here is the table in question, the table has 1,000,000 entries. There are no indexes on the table.

+-----------+-----------------------------------------------+------+-----+---------+-------+
| Field     | Type                                          | Null | Key | Default | Extra |
+-----------+-----------------------------------------------+------+-----+---------+-------+
| firstname | varchar(30)                                   | NO   |     | NULL    |       |
| lastname  | varchar(30)                                   | NO   |     | NULL    |       |
| year      | enum('FRESHMAN','SOPHMORE','JUNIOR','SENIOR') | NO   |     | NULL    |       |
| gpa       | decimal(3,2) unsigned                         | NO   |     | NULL    |       |
+-----------+-----------------------------------------------+------+-----+---------+-------+
Orion447
  • 351
  • 4
  • 16
  • 1
    With no indexes, the first query is going to be faster, it requires 1 full scan to fulfll both criteria, the union is two full scans. – Stu Dec 10 '21 at 21:51
  • @stu Thank you I understand it a bit more now. – Orion447 Dec 10 '21 at 22:00

0 Answers0