Demiel: I ran it like this: mysqlcheck –all-databases –verbose
Demiel: Salle: ok, so what are my options then?
Clyatt: Zamba: Your options are: 1 Read the manual; 2 Hire someone who knows MySQL to maintain your server
Clyatt: Zamba: Meanwile if you can SELECT from thsi table dump it; drop it and reimport it again. If you can’t SELECT you better have a good backup
Demiel: Salle: i performed a successful mysqldump
Demiel: Salle: so i guess that means that it was successful?
Rumery: Check the data you got if it makes sense
Demiel: I’ll try importing it on a different mysql server
Demiel: And perform some selects on it instead
Demiel: Salle: will a successful ‘select count* from history’ mean that the table is in a state where i can drop the database and then recreate it from the mysqldump?
Demiel: Looks like it worked:
Demiel: 1 row in set 5 min 23.36 sec
Mantilia: Rumery, products.id cannot be null, it is primary key. I tried count1 out tho, no difference
Hoekman: I want to know how this could be improved upon. It seems to be simple, but I’m clueless. http://pastie.org/private/geemclti2kp8fcdh8ribq
Rumery: Betterton, you count all products which have matching campaign this way? Then I do not know how to make it faster than what you already have
Rumery: Betterton, is there any chance that a product will have a NOT NULL value in “products.campaign_id” but still no record for that campaign in “campaign_countries” ? = do you actually need the join or just existence of a campaign might be enough?
Abrantes: Rumery, Yes, that is what I am trying to do. It is currently very slow 5s. Is there some fundamental design mistake I have made?
Elks: Rumery, I just need existence of a campaign
Rumery: Betterton, you use InnoDB? InnoDB when counting rows has to work with transactions etc, so it has to actually count the rows which are “visible” in the current transaction, so it may take time when there are many of them – does it make sense for you to count “all” of them without any additional WHERE conditions like specific country or campaigns of specific type
Prahm: Myisam optimization only applies to SELECT COUNT* FROM table; query
Morlan: Rumery, campaign_countries is MyISAM and products is InnoDB
Rozar: Betterton: you can’t make that query faster
Germano: Do you want to SELECT COUNTDISTINCT products.id ?
Herek: Your query is counting product campaign countries
Weyrauch: I only want to count the products
Carlow: Which has a certain country in it’s campign
Keywan: Boleware, this is what I am doing at the moment: http://pastie.org/private/gkctsixipcjzir4ilksaeg
Gillespi: Betterton: but it is a different query
Streck: Explain it, not the one before
Rumery: Betterton, adding a WHERE clause may change the plan a lot – check it for this query – you will see a different order of tables probably
Noffsinger: Alright, I just tried to simplify it. I guess that what’s not to do :
Rumery: And as Boleware wrote – you currently count one product multiple times if the campaign has multiple countries ***ociated maybe it cannot happen now but you are not saying
Reddig: Rumery, it can happen, yes
Rumery: Betterton, yep, if you count everything, the results will be very different from counting some specific subset unless that subset makes a big part of your data
Wreede: Boleware, here is explain for the correct query: http://pastie.org/private/1danpyajslwepmcxfhs13w
Simkin: Have you got an index on campaign_countries.country_id ?
Clyatt: Betterton: It is much easier if you paste the EXPLAIN SELECT and result of the EXPLAIN togehter and also SHOW CREATE TABLE for both tables
Clyatt: Betterton: Are you sure you want to count the number of rows in the result of join?
Petosa: Boleware, I have a multi-col index including country_id/campaign_id