有没有更好的方法来执行这些 mysql 查询？

Question

目前我有三个不同的表和三个不同的查询，它们彼此非常相似，具有几乎相同的连接。我试图将所有这三个查询合并到一个查询中，但到目前为止还没有取得多大成功。如果有人有更好的解决方案或指出方向，我将非常高兴。谢谢。

0.0013       
SELECT `ilan_genel`.`id`, `ilan_genel`.`durum`, `ilan_genel`.`kategori`, `ilan_genel`.`tip`, `ilan_genel`.`ozellik`, `ilan_genel`.`m2`, `ilan_genel`.`fiyat`, `ilan_genel`.`baslik`, `ilan_genel`.`ilce`, `ilan_genel`.`mahalle`, `ilan_genel`.`parabirimi`, `kgsim_ilceler`.`isim` as ilce, (
 SELECT ilanresimler.resimlink
 FROM ilanresimler
 WHERE ilanresimler.ilanid = ilan_genel.id LIMIT 1
 ) AS resim
FROM (`ilan_genel`)
LEFT JOIN `kgsim_ilceler` ON `kgsim_ilceler`.`id` = `ilan_genel`.`ilce`
ORDER BY `id` desc
LIMIT 30 
0.0006       
SELECT `video`.`id`, `video`.`url`, `ilan_genel`.`ilce`, `ilan_genel`.`tip`, `ilan_genel`.`m2`, `ilan_genel`.`ozellik`, `ilan_genel`.`fiyat`, `ilan_genel`.`parabirimi`, `ilan_genel`.`kullanici`, `ilanresimler`.`resimlink` as resim, `uyeler`.`isim` as isim, `uyeler`.`soyisim` as soyisim, `kgsim_ilceler`.`isim` as ilce
FROM (`video`)
LEFT JOIN `ilan_genel` ON `ilan_genel`.`id` = `video`.`id`
LEFT JOIN `kgsim_ilceler` ON `kgsim_ilceler`.`id` = `ilan_genel`.`ilce`
LEFT JOIN `ilanresimler` ON `ilanresimler`.`id` = `ilan_genel`.`resim`
LEFT JOIN `uyeler` ON `uyeler`.`id` = `ilan_genel`.`kullanici`
ORDER BY `siralama` desc
LIMIT 30 
0.0005       
SELECT `sanaltur`.`id`, `ilan_genel`.`ilce`, `ilan_genel`.`tip`, `ilan_genel`.`m2`, `ilan_genel`.`ozellik`, `ilan_genel`.`fiyat`, `ilan_genel`.`parabirimi`, `ilan_genel`.`kullanici`, `ilanresimler`.`resimlink` as resim, `uyeler`.`isim` as isim, `uyeler`.`soyisim` as soyisim, `kgsim_ilceler`.`isim` as ilce
FROM (`sanaltur`)
LEFT JOIN `ilan_genel` ON `ilan_genel`.`id` = `sanaltur`.`id`
LEFT JOIN `kgsim_ilceler` ON `kgsim_ilceler`.`id` = `ilan_genel`.`ilce`
LEFT JOIN `ilanresimler` ON `ilanresimler`.`id` = `ilan_genel`.`resim`
LEFT JOIN `uyeler` ON `uyeler`.`id` = `ilan_genel`.`kullanici`
ORDER BY `siralama` desc
LIMIT 30 

Answer 1

这实际上是三个非常不同的查询。我认为您无法将它们有效地结合起来。而且，它们对我来说似乎相当快。

但是，如果您想尝试优化每个单独的查询，则可以使用 EXPLAIN SELECT 来了解每个查询是否使用了适当的索引。

例如：

EXPLAIN SELECT * 
FROM A 
WHERE foo NOT IN (1,4,5,6);

可能会产生：

+----+-------------+-------+------+---------------
| id | select_type | table | type | possible_keys 
+----+-------------+-------+------+---------------
|  1 | SIMPLE      | A     | ALL  | NULL          
+----+-------------+-------+------+---------------

+------+---------+------+------+-------------+
| key  | key_len | ref  | rows | Extra       |
+------+---------+------+------+-------------+
| NULL | NULL    | NULL |    2 | Using where |
+------+---------+------+------+-------------+

在这种情况下，查询没有 possible_keys，因此不使用（或 NULL）key 来执行查询。这是您感兴趣的 key 列。

更多信息请参见：

• http://dev.mysql.com/doc/refman/5.5/en/explain.html
• http://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html