Optimize, Fix MySQL Join Query

[digital-ether]

I'm trying to create an Alpha Numeric List of links. Each alphabet links to a page that will display rows whose titles start with that alphabet or numeral.
For each alphabet, I want to get the number of titles that start with that alphabet. This allows me to link only the alphabets that actually have corresponding rows.

Here is the structure of the table:

CREATE TABLE IF NOT EXISTS `jos_mediaboss` (
  `id` bigint(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL,
  `catid` bigint(11) NOT NULL,
  `published` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  ;

The query I'm doing is a LEFT JOIN repeated on the table for each alphabet.

SELECT count(ma.id) AS ca, 
 count(mb.id) AS cb, 
 count(mc.id) AS cc, 
 count(md.id) AS cd, 
 count(me.id) AS ce, 
 count(mf.id) AS cf, 
 count(mg.id) AS cg, 
 count(mh.id) AS ch, 
 count(mi.id) AS ci, 
 count(mj.id) AS cj, 
 count(mk.id) AS ck, 
 count(ml.id) AS cl, 
 count(mm.id) AS cm, 
 count(mn.id) AS cn, 
 count(mo.id) AS co, 
 count(mp.id) AS cp, 
 count(mq.id) AS cq, 
 count(mr.id) AS cr, 
 count(ms.id) AS cs, 
 count(mt.id) AS ct, 
 count(mu.id) AS cu, 
 count(mv.id) AS cv, 
 count(mw.id) AS cw, 
 count(mx.id) AS cx, 
 count(my.id) AS cy, 
 count(mz.id) AS cz 
 FROM jos_mediaboss AS m 
 LEFT JOIN jos_mediaboss AS ma ON (ma.title LIKE 'a%'  AND ma.published = 1)  
  LEFT JOIN jos_mediaboss AS mb ON (mb.title LIKE 'b%'  AND mb.published = 1)  
  LEFT JOIN jos_mediaboss AS mc ON (mc.title LIKE 'c%'  AND mc.published = 1)  
  LEFT JOIN jos_mediaboss AS md ON (md.title LIKE 'd%'  AND md.published = 1)  
  LEFT JOIN jos_mediaboss AS me ON (me.title LIKE 'e%'  AND me.published = 1)  
  LEFT JOIN jos_mediaboss AS mf ON (mf.title LIKE 'f%'  AND mf.published = 1)  
  LEFT JOIN jos_mediaboss AS mg ON (mg.title LIKE 'g%'  AND mg.published = 1)  
  LEFT JOIN jos_mediaboss AS mh ON (mh.title LIKE 'h%'  AND mh.published = 1)  
  LEFT JOIN jos_mediaboss AS mi ON (mi.title LIKE 'i%'  AND mi.published = 1)  
  LEFT JOIN jos_mediaboss AS mj ON (mj.title LIKE 'j%'  AND mj.published = 1)  
  LEFT JOIN jos_mediaboss AS mk ON (mk.title LIKE 'k%'  AND mk.published = 1)  
  LEFT JOIN jos_mediaboss AS ml ON (ml.title LIKE 'l%'  AND ml.published = 1)  
  LEFT JOIN jos_mediaboss AS mm ON (mm.title LIKE 'm%'  AND mm.published = 1)  
  LEFT JOIN jos_mediaboss AS mn ON (mn.title LIKE 'n%'  AND mn.published = 1)  
  LEFT JOIN jos_mediaboss AS mo ON (mo.title LIKE 'o%'  AND mo.published = 1)  
  LEFT JOIN jos_mediaboss AS mp ON (mp.title LIKE 'p%'  AND mp.published = 1)  
  LEFT JOIN jos_mediaboss AS mq ON (mq.title LIKE 'q%'  AND mq.published = 1)  
  LEFT JOIN jos_mediaboss AS mr ON (mr.title LIKE 'r%'  AND mr.published = 1)  
  LEFT JOIN jos_mediaboss AS ms ON (ms.title LIKE 's%'  AND ms.published = 1)  
  LEFT JOIN jos_mediaboss AS mt ON (mt.title LIKE 't%'  AND mt.published = 1)  
  LEFT JOIN jos_mediaboss AS mu ON (mu.title LIKE 'u%'  AND mu.published = 1)  
  LEFT JOIN jos_mediaboss AS mv ON (mv.title LIKE 'v%'  AND mv.published = 1)  
  LEFT JOIN jos_mediaboss AS mw ON (mw.title LIKE 'w%'  AND mw.published = 1)  
  LEFT JOIN jos_mediaboss AS mx ON (mx.title LIKE 'x%'  AND mx.published = 1)  
  LEFT JOIN jos_mediaboss AS my ON (my.title LIKE 'y%'  AND my.published = 1)  
  LEFT JOIN jos_mediaboss AS mz ON (mz.title LIKE 'z%'  AND mz.published = 1) LIMIT 1

This is query is generated dynamically.

It returns something like:

+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
| ca | cb | cc | cd | ce | cf | cg | ch | ci | cj | ck | cl | cm | cn | co | cp | cq | cr | cs | ct | cu | cv | cw | cx | cy | cz |
+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
|  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 |  0 | 84 |  0 |  0 |  0 |  0 |  0 | 84 | 84 |  0 |  0 |  0 |  0 |  0 |  0 |  0 | 
+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+----+
1 row in set (0.02 sec)

The problem is that I the counts for each row that starts with an alphabet is wrong. There are only 7 entries in the table, but the count() or each "id" returns 84 or some larger number. if the is at least one entry.

ie:

This is the count:

count(ma.id) AS ca,

for this join:

LEFT JOIN jos_mediaboss AS ma ON (ma.title LIKE 'a%'  AND ma.published = 1)

or is it?

If there are rows starting with a, the returned column 'ca' will be 84 instead of the count just for that JOIN condition, as I want.

[digital-ether]

I've ended up using this query, which is basically just the subqueries wrapped up into one query, but I'd like to do this with JOINs if possible.

SELECT (SELECT COUNT(ma.id) FROM jos_mediaboss AS ma WHERE ma.title LIKE 'a%'  AND ma.published = 1) AS ca, 
 (SELECT COUNT(mb.id) FROM jos_mediaboss AS mb WHERE mb.title LIKE 'b%'  AND mb.published = 1) AS cb, 
 (SELECT COUNT(mc.id) FROM jos_mediaboss AS mc WHERE mc.title LIKE 'c%'  AND mc.published = 1) AS cc, 
 (SELECT COUNT(md.id) FROM jos_mediaboss AS md WHERE md.title LIKE 'd%'  AND md.published = 1) AS cd, 
 (SELECT COUNT(me.id) FROM jos_mediaboss AS me WHERE me.title LIKE 'e%'  AND me.published = 1) AS ce, 
 (SELECT COUNT(mf.id) FROM jos_mediaboss AS mf WHERE mf.title LIKE 'f%'  AND mf.published = 1) AS cf, 
 (SELECT COUNT(mg.id) FROM jos_mediaboss AS mg WHERE mg.title LIKE 'g%'  AND mg.published = 1) AS cg, 
 (SELECT COUNT(mh.id) FROM jos_mediaboss AS mh WHERE mh.title LIKE 'h%'  AND mh.published = 1) AS ch, 
 (SELECT COUNT(mi.id) FROM jos_mediaboss AS mi WHERE mi.title LIKE 'i%'  AND mi.published = 1) AS ci, 
 (SELECT COUNT(mj.id) FROM jos_mediaboss AS mj WHERE mj.title LIKE 'j%'  AND mj.published = 1) AS cj, 
 (SELECT COUNT(mk.id) FROM jos_mediaboss AS mk WHERE mk.title LIKE 'k%'  AND mk.published = 1) AS ck, 
 (SELECT COUNT(ml.id) FROM jos_mediaboss AS ml WHERE ml.title LIKE 'l%'  AND ml.published = 1) AS cl, 
 (SELECT COUNT(mm.id) FROM jos_mediaboss AS mm WHERE mm.title LIKE 'm%'  AND mm.published = 1) AS cm, 
 (SELECT COUNT(mn.id) FROM jos_mediaboss AS mn WHERE mn.title LIKE 'n%'  AND mn.published = 1) AS cn, 
 (SELECT COUNT(mo.id) FROM jos_mediaboss AS mo WHERE mo.title LIKE 'o%'  AND mo.published = 1) AS co, 
 (SELECT COUNT(mp.id) FROM jos_mediaboss AS mp WHERE mp.title LIKE 'p%'  AND mp.published = 1) AS cp, 
 (SELECT COUNT(mq.id) FROM jos_mediaboss AS mq WHERE mq.title LIKE 'q%'  AND mq.published = 1) AS cq, 
 (SELECT COUNT(mr.id) FROM jos_mediaboss AS mr WHERE mr.title LIKE 'r%'  AND mr.published = 1) AS cr, 
 (SELECT COUNT(ms.id) FROM jos_mediaboss AS ms WHERE ms.title LIKE 's%'  AND ms.published = 1) AS cs, 
 (SELECT COUNT(mt.id) FROM jos_mediaboss AS mt WHERE mt.title LIKE 't%'  AND mt.published = 1) AS ct, 
 (SELECT COUNT(mu.id) FROM jos_mediaboss AS mu WHERE mu.title LIKE 'u%'  AND mu.published = 1) AS cu, 
 (SELECT COUNT(mv.id) FROM jos_mediaboss AS mv WHERE mv.title LIKE 'v%'  AND mv.published = 1) AS cv, 
 (SELECT COUNT(mw.id) FROM jos_mediaboss AS mw WHERE mw.title LIKE 'w%'  AND mw.published = 1) AS cw, 
 (SELECT COUNT(mx.id) FROM jos_mediaboss AS mx WHERE mx.title LIKE 'x%'  AND mx.published = 1) AS cx, 
 (SELECT COUNT(my.id) FROM jos_mediaboss AS my WHERE my.title LIKE 'y%'  AND my.published = 1) AS cy, 
 (SELECT COUNT(mz.id) FROM jos_mediaboss AS mz WHERE mz.title LIKE 'z%'  AND mz.published = 1) AS cz

[digital-ether]

For anyone interested:

I finally ended up with:

SELECT SUBSTR(m.title, 1, 1) AS alpha, COUNT(DISTINCT(m.id)) AS total  
FROM com_mediaboss AS m 
WHERE m.published = 1
GROUP BY alpha
ORDER BY alpha

Which is probably a whole lot more efficient then the previous two queries.

Basically I was looking at the query from the wrong angle and it was much simpler then that.