| arjenAU | beeyatch: oh, what also helps is normalisation. since it's a tablescan, it'd be best to not have lots of other columns in the table js_: no. bad design |
| polo_ | Sapient, i found another way, something i overlooked earlier, another user.. thanks for all your help |
| hyakki | whats the best data Type (longtext?)to use for lots of html? and is there anyway to get the db size down? |
| beeyatch | arjenAU: thanks for your help. The table i do where like %% on is about 800mb+ big |
| arjenAU | beeyatch: not a good idea. |
| beeyatch | i think using somekind of fulltext indexing system is a good idea arjenAU: i cant avoid it |
| arjenAU | yes you can. better design. |
| beeyatch | jeh |
| arjenAU | either table design, or infrastructure |
| beeyatch | eheh ;) |
| arjenAU | it's not smth any RDBMS is fab at. |
| beeyatch | having the whole db on ram would be cool :) |
| arjenAU | also nonsensical. but can be done. CREATE TABLE blah ENGINE=MEMORY SELECT * FROM badtable; make sure max_heap_table_size is suffciiently large |
| beeyatch | its created alrdy can i do it by alter? |
| arjenAU | you don't want the whole table in mem. its silly |
| beeyatch | jeh true |
| arjenAU | MEMORY can't deal with large textfields like that |
| beeyatch | i have another idea to optimize it its varchar 255 |
| arjenAU | and you dn't want the other stuff wth it then in mem it's a CHAR 255. bad design anyway |
| beeyatch | k SELECT`key` FROM collections WHERE subject LIKE '%Spiderman.3.DVDR-Replica%' HAVING ((SUM(parts)/SUM(totalparts) * 100) > 95) LIMIT 1 oO :D is HAVING (sum...) stuff slow? |
| arjenAU | beeyatch: having deals with the provisional resultset then tosses away rows that don't match. beeyatch: whereas WHERE decides what to retrieve in the first place. so HAVING is not pretty, as such. but someitmes necessary. like with SUM() the LIMIT 1 looks odd also. why not just ORDER BY something DESC LIMIT 1 it's a very ugly query, and i reckon it's mostly the search that is the issue. you don't want to search like that |
| hydrozen | hey, im running a query that returns the sum of a couple of colums (ex: select sum(col1), sum(col2), sum(col3)) ... what can I do if i also want to return the total of these sums?? |
| NemesisD | is there a way to use LIKE to match regular expressions or at least strings with wildcards? nm i got it, its like SELECT * FROM t1 WHERE col REGEXP 'foo.*' |