Large SELECTS

[f1r3fl3x]

How does a search script fetch results when the db has over 450 000 entries in different tables in the database (http://metrolyrics.com). In my database i have abot 300 000 entries and i wanted to make a search script, but i failed. Here's my code. Someone please HELP  :'(

 

SELECT count(*) as woo FROM `a`, `a_songs`, `b`, `b_songs`, `c`, `c_songs`, `d`, `d_songs`, `e`, `e_songs`, `f`, `f_songs`, `g`, `g_songs`, `h`, `h_songs`, `i`, `i_songs`, `j`, `j_songs`, `k`, `k_songs`, `l`, `l_songs`, `m`, `m_songs`, `n`, `n_songs`, `o`, `o_songs`, `p`, `p_songs`, `q`, `q_songs`, `r`, `r_songs`, `s`, `s_songs`, `t`, `t_songs`, `u`, `u_songs`, `v`, `v_songs`, `w`, `w_songs`, `x`, `x_songs`, `y`, `y_songs`, `z`, `z_songs`, `1`, `1_songs`
WHERE
(`a`.`title` LIKE '%aba%') OR (`a_songs`.`title` LIKE '%aba%') OR (`b`.`title` LIKE '%aba%') OR (`b_songs`.`title` LIKE '%aba%') OR (`c`.`title` LIKE '%aba%') OR (`c_songs`.`title` LIKE '%aba%') OR (`d`.`title` LIKE '%aba%') OR (`d_songs`.`title` LIKE '%aba%') OR (`e`.`title` LIKE '%aba%') OR (`e_songs`.`title` LIKE '%aba%') OR (`f`.`title` LIKE '%aba%') OR (`f_songs`.`title` LIKE '%aba%') OR (`g`.`title` LIKE '%aba%') OR (`g_songs`.`title` LIKE '%aba%') OR (`h`.`title` LIKE '%aba%') OR (`h_songs`.`title` LIKE '%aba%') OR (`i`.`title` LIKE '%aba%') OR (`i_songs`.`title` LIKE '%aba%') OR (`j`.`title` LIKE '%aba%') OR (`j_songs`.`title` LIKE '%aba%') OR (`k`.`title` LIKE '%aba%') OR (`k_songs`.`title` LIKE '%aba%') OR (`l`.`title` LIKE '%aba%') OR (`l_songs`.`title` LIKE '%aba%') OR (`m`.`title` LIKE '%aba%') OR (`m_songs`.`title` LIKE '%aba%') OR (`n`.`title` LIKE '%aba%') OR (`n_songs`.`title` LIKE '%aba%') OR (`o`.`title` LIKE '%aba%') OR (`o_songs`.`title` LIKE '%aba%') OR (`p`.`title` LIKE '%aba%') OR (`p_songs`.`title` LIKE '%aba%') OR (`q`.`title` LIKE '%aba%') OR (`q_songs`.`title` LIKE '%aba%') OR (`r`.`title` LIKE '%aba%') OR (`r_songs`.`title` LIKE '%aba%') OR (`s`.`title` LIKE '%aba%') OR (`s_songs`.`title` LIKE '%aba%') OR (`t`.`title` LIKE '%aba%') OR (`t_songs`.`title` LIKE '%aba%') OR (`u`.`title` LIKE '%aba%') OR (`u_songs`.`title` LIKE '%aba%') OR (`v`.`title` LIKE '%aba%') OR (`v_songs`.`title` LIKE '%aba%') OR (`w`.`title` LIKE '%aba%') OR (`w_songs`.`title` LIKE '%aba%') OR (`x`.`title` LIKE '%aba%') OR (`x_songs`.`title` LIKE '%aba%') OR (`y`.`title` LIKE '%aba%') OR (`y_songs`.`title` LIKE '%aba%') OR (`z`.`title` LIKE '%aba%') OR (`z_songs`.`title` LIKE '%aba%') OR (`1`.`title` LIKE '%aba%') OR (`1_songs`.`title` LIKE '%aba%') LIMIT 0,15

[kickstart]

Hi

 

Why the massive number of tables? With that syntax you will get a massive number of results as you are not joining the tables together.

 

Not sure why the "count(*)", or why you are specifying a limit 0, 15 when you have used an aggregate function

 

Simple solution would seem to be to have selects against each table and union the results together:-

 

SELECT title, 'table a' 
FROM `a`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table a_songs' 
FROM `a_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table b' 
FROM `b`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table b_songs' 
FROM `b_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table c' 
FROM `c`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table c_songs' 
FROM `c_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table d' 
FROM `d`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table d_songs' 
FROM `d_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table e' 
FROM `e`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table e_songs' 
FROM `e_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table f' 
FROM `f`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table f_songs' 
FROM `f_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table g' 
FROM `g`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table g_songs' 
FROM `g_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table h' 
FROM `h`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table h_songs' 
FROM `h_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table i' 
FROM `i`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table i_songs' 
FROM `i_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table j' 
FROM `j`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table j_songs' 
FROM `j_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table k' 
FROM `k`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table k_songs' 
FROM `k_songs`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table l' 
FROM `l`
WHERE title LIKE '%aba%'
UNION
SELECT title, 'table l_songs' 
FROM `l_songs`
WHERE title LIKE '%aba%'

 

All the best

 

Keith

[f1r3fl3x]

Thx @kickstart, i don't know sql that good, i''m a lot better in php, but you can't use one without the other. So, i need this code for a search form, to pick up results from the database and order them by match. Is the code you ggave me going to work?

[kickstart]

Hi

 

That would give you all the matches and a field saying which table the match was found on. There is no sort on there (but one would be easy to add). Also there is no ID field being brought back for any of the matches, so if you wanted to link to anything it wouldn't be that tidy.

 

All the best

 

Keith

[f1r3fl3x]

thanks @kickstart. I can't test it right now, but as soon as i do, i'll tell you the result

BTW, is LIKE faster or REGEXP, becouse like i said the database is large and it takes a great amount of time to execute the query. And is there a way with regexp, to get only a part of a text in a row?

[kickstart]

Hi

 

Never used regular expressions in SQL and not sure how to (and I suck at regular expressions!).

 

All the best

 

Keith

[f1r3fl3x]

Ok, no matter, i found some lessons, and it isn't so hard. My final question is what would be faster to search in a database with artist and songs, where each songs is in a diffrent row, or each artist is in a diffrent row, and the songs are listed in a field separated by a comma ? An can VIEWS speed up the SELECT queries ?

[kickstart]

Hi

 

If you mean that all the songs for an artist an in a single field seperated by commas then I suspect that any solution is going to be poor as it will be difficult to index anything.

 

All the best

 

Keith