有没有更好的方法来执行这些mysql查询? [英] is there better way to do these mysql queries?

问题描述

当前，我有三个不同的表和三个不同的查询，它们彼此之间的连接几乎相同，非常相似.我试图将所有三个查询与一个查询结合在一起，但是到目前为止，并没有取得太大的成功.如果有人有更好的解决方案或指出的方向，我将感到非常高兴.谢谢.

Currently i have three different tables and three different queries, which are very similiar to each others with almost same joins. I was trying to combine all that three queries with in one query, so far not much success though. I will be very happy if someone has better solution or a direction to point. Thanks.

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 

推荐答案

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

These are actually three very different queries. I don't think you will be able to usefully combine them. Also, they seem pretty fast to me.

但是，如果要尝试优化每个查询，可以使用EXPLAIN SELECT来查找每个查询如何使用适当的索引.

However, if you want to try to optimize each individual query, you can use EXPLAIN SELECT to find out how if each query uses appropriate indexes or not.

例如:

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列.

In this case, the query had no possible_keys and therefore used no (or NULL) key to do the query. It's the key column you'd be interested in.

更多信息在这里:

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

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

这篇关于有没有更好的方法来执行这些mysql查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！