0

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
mirza
  • 5,685
  • 10
  • 43
  • 73
  • 1
    isn't it too slow http://stackoverflow.com/questions/870080/why-are-union-queries-so-slow-in-mysql – mirza Nov 18 '11 at 10:50

1 Answers1

3

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.

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.

For example:

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

Might yield:

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

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

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.

More information here:

Gustav Bertram
  • 14,591
  • 3
  • 40
  • 65
  • thanks for your insightful answer, that's a really nice explanation. however, i am already using indexes and queries are fast as it can be but i was wondering is there a better way to do that. i don't want to send 3 different queries in each time to database and also union queries are seemed to me a little bit slower than original solution. – mirza Nov 19 '11 at 04:45
  • Nothing wrong with sending three queries. If it becomes an issue, and the data does not have to be fresh, then try caching the results. – Gustav Bertram Nov 19 '11 at 09:07
  • The only thing I can think of to make the queries slightly faster is to use [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html). Still, if you have optimized the queries, then possibly you should look for other places to optimize in the code by using profiling, or by caching query results. It depends on the system you're using. Worry about big optimizations, not small ones. – Gustav Bertram Nov 19 '11 at 14:34