How to improve the query efficiency of MariaDB

recently, after the company migrated sql server to mariadb, data, it found that the query efficiency was much lower.

there are now multiple data tables, each with more than 10w rows of data, and the data in the table is not unique and the index cannot be used.

with the most common LEFT JOIN,sql server, it takes a few seconds to get a result, while mariadb takes hundreds of seconds.

how should mariadb optimize configuration and query? Baidu has no suitable answer.

this is the query result of mariadb
clipboard.png

sql server
clipboard.png

this is a query statement. The number is not unique and the index cannot be used. 

SELECT a.*, b.* FROM  a, b WHERE a.type = "up" AND b.type = "up" AND a.number = b.number
Mysql
Mar.20,2021

No matter whether it is unique or not, it is indexed. Anyway, mysql has all kinds of keys and indexes, so just add a common index directly. In addition, even if there is no index, the data should not be so slow.

EXPLAIN SQL
explain SELECT a.forth, b. * FROM a, b WHERE a.type = 'up' AND b.type =' up' AND a.number = b.number;
execute to take a look at the result.
this sentence SQL is essentially JOIN
it is recommended to write: 

SELECT * from a left join (select * from b where b.type='up') c on a.number = c.number where a.type='up'

Previous: React router goback is not valid in iOS webview

Next: How does vs code remove the decorator warning?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1e9c423-4769a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1e9c423-4769a.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?